Need Help in Currency Formatting

Database

Need Help in Currency Formatting

 Hi,

 

I am failry new to Teradata. I am trying to format a Currency Value to be displayed in French Canadian format. input is a decimal type.

Example: Inputs can be

123       ===> SHould be displayed as 123,00$

123.5  ====> Should be displayed as 123,50$

12345.65 ====> Should be displayed as 12 345,65$

Now Default Separater is a comma and default radix character is a dot. which means below Format is returning the value as 123.00, 123.50 and 12,345.60. 

SELECT TRIM(12345678.5 (FORMAT 'GZ(9)D99$'))

Result: 12,345,678.50$

My Question is how to use a space for thousand separater and a comma for radix character from within SQL as I do not want to change the settings in SDF because I also have to display some of the values in American format.

 


Accepted Solutions
Teradata Employee

Re: Need Help in Currency Formatting

format'zzzBzzzBzz9,99$'   should work regardless of the GroupSeparator settings in SDF, although I had trouble putting the '$' at the end of the string - it seemed to want it at the beginning of the string, even though the SQL Data Types and Literals manual (at infor.teradata.com) indicates it should work anywhere.  Anyway, 'B' and ',' override the global settings.  Try  this and see how far you get....

1 ACCEPTED SOLUTION
5 REPLIES
Teradata Employee

Re: Need Help in Currency Formatting

format'zzzBzzzBzz9,99$'   should work regardless of the GroupSeparator settings in SDF, although I had trouble putting the '$' at the end of the string - it seemed to want it at the beginning of the string, even though the SQL Data Types and Literals manual (at infor.teradata.com) indicates it should work anywhere.  Anyway, 'B' and ',' override the global settings.  Try  this and see how far you get....

Teradata Employee

Re: Need Help in Currency Formatting

Have you tried to use the DualCurrency formatting capabilites (e.g., O, U, or A format elements) in the SDF?

 

-Dave

Re: Need Help in Currency Formatting


GJColeman wrote:

format'zzzBzzzBzz9,99$'   should work regardless of the GroupSeparator settings in SDF, although I had trouble putting the '$' at the end of the string - it seemed to want it at the beginning of the string, even though the SQL Data Types and Literals manual (at infor.teradata.com) indicates it should work anywhere.  Anyway, 'B' and ',' override the global settings.  Try  this and see how far you get....



Thank You for the response. FORMAT 'ZZZBZZZBZZ9,99$') did not work for me as well. but below code worked.

 

SELECT TRIM(12345678 (FORMAT 'ZZZBZZZBZZ9,99')) || '$'

Re: Need Help in Currency Formatting


David_Craig1 wrote:

Have you tried to use the DualCurrency formatting capabilites (e.g., O, U, or A format elements) in the SDF?

 

-Dave



Thanks for your response, but I do not have access to modify SDF. I think TRIM(12345678 (FORMAT 'ZZZBZZZBZZ9,99')) || '$' will do the job. 

Teradata Employee

Re: Need Help in Currency Formatting

Ideally, the solution would use internationalized formatting elements such that the FORMAT clause does not need to change with the locale. The SDF is a system level specification for locale data which requires a DBS restart to apply the changes. If you need session level locale data support (e.g., en_US and fr_CA), submit a request for enhancement through your support representative.

 

-Dave