How to Remove Leading Zeros From Dates in Oracle

In Oracle Database, you can use the fm (fill mode) format modifier to suppress any leading zeroes that might be applied to a date. This format modifier suppresses padding, which includes leading zeros and trailing blanks.

To use the fm format modifier, include it in your format model when specifying how dates should be formatted. For example when using the TO_CHAR() function to format dates, or in NLS parameters such as the NLS_DATE_FORMAT parameter when setting the date format for your current session.

Continue reading

Fix “Conversion failed when converting the varchar value” When Trying to Concatenate in SQL Server

If you get error Msg 245 that reads “Conversion failed when converting the varchar value…” when trying to perform a concatenation operation in SQL Server, it’s likely that you’re attempting to concatenate a string and a number.

Doing this will result in an error, due to SQL Server trying to add the string and number instead of concatenate them.

To fix this, either convert the number to a string, or use a function like CONCAT() or CONCAT_WS() to perform the concatenation.

Continue reading

Format a Number as Currency in SQL

Some DBMSs have functions that allow us to format numbers as currency just by passing the appropriate format string. This converts the number to a string with the applicable currency symbol, group separator, and decimal point (if relevant).

Other DBMSs don’t make it that easy, and you need to do a bit of work first.

Below are examples of using SQL to format numbers as currency in some of the most popular DBMSs.

Continue reading