sqlintersystems

Convert money to text and keep format

I need to show a dollar range in a query but when I concatenate the values I lose the comma in the output.

select
'$'||CAST(ROUND(MIN(db.INITIAL_BALANCE),-1) AS money)
||' to '||
'$'||CAST(ROUND(MAX(db.INITIAL_BALANCE),-1) AS money) AS Balance_Range

the output is $2060 to $73690

desired output $2,060 to $73,690

Any suggestions on this?

Intersystems Cache is my database

Solution 
  • '$'||TRIM(tochar(MIN(db.Account_Balance),'9,999,999'))|| ' to ' ||'$'||TRIM(tochar(max(db.Account_Balance),'9,999,999'))

    gave me what I needed .. Thanks!