SQL Currency Formatting

Question

I am running into an issue when trying to convert my query to currency format. Below is the code and I keep ending up with two different error messages when attempting the conversion on the 'purchases' sum.

When using:

sum(case 
when trandate between '2015-01-01' and '2015-03-31'
     then format(purchases, 'C', 'en-US') 
else 0 end) as q1 

I get error message 102

When using:

sum(case 
when trandate between '2015-01-01' and '2015-03-31'
     then ('$' + CONVERT(varchar(12), purchases, 1))
else 0 end) as 'Q1 2015'

I get error message 245.

I cant seem to find a solution to either. When just simply querying the data at a line level and using both formats I get a $xxx returned. Any help would be greatly appreciated.

Cheers

Answer 1

when using CASE expression, the result expression data type will be converted from one to another to same data type

case when trandate between '2015-01-01' and '2015-03-31'
     then format(purchases, 'C', 'en-US') 
     else 0 
     end

in the above case expression, you have 2 different data type in the result. One is STRING the other is INTEGER. SQL Server will convert the string to integer. Which it is unable to and throw out that error.

You should perform the formatting of value in your front end application where the result is display.

If you really had to do it in T-SQL, change the else to '0'

else '0'

EDIT 1 :

As you have the SUM (), you should convert after the SUM not before. Else you will be applying SUM() on string which does not work also.

format (
         SUM (case when trandate between '2015-01-01' and '2015-03-31'
                   then purchases -- format(purchases, 'C', 'en-US') 
                   else 0 
                   end) , 'C', 'en-US')