Guru: Formatting Numbers and Dates/Times/Timestamps in SQL

January 13, 2020 Paul Tuohy

In this article, I want to share with you an SQL scalar function that I happen to have been using quite a bit recently. At times, when using an SQL select statement, you may want to format a number or date. Something along the same lines as using the %EDITC or %EDITW built in functions in RPG or the EDTCDE or EDTWRD keywords in DDS. In SQL we can use the VARCHAR_FORMAT or TO_CHAR (they are synonyms for each other – both work exactly the same way) scalar function to provide similar functionality.

Since they are synonyms for each other, I am going to use TO_CHAR in the rest of the article, but feel free to substitute VARCHAR_FORMAT in its place.

To demonstrate the use of TO_CHAR I created four global variables and populated them as follows:

create variable stamp timestamp;
create variable isdate date;
create variable amount decimal (7, 2);
create variable negative_amount decimal (7, 2);

set stamp = current timestamp;  -- 2019-12-29 16:32:40.889085
set isdate = current date;      -- 2019-12-29
set amount = 1234.56;           -- 1234.56
set negative_amount = -1234.56; -- -1234.56

The format of TO_CHAR is:

TO_CHAR(numeric expression or timestamp expression, format string)

The first parameter is the numeric or date/time/timestamp expression to be evaluated and the second parameter is the required format. The format is a template as to how the value is to be formatted

The formatting rules are, as one would expect, different for a number as opposed to a date/time/timestamp. Let’s start with numbers.

The template represents each significant digit using a 0 or a 9. With 0, leading zeros are shown as zero. With 9, leading zeros are replaced with blanks.

values 
 to_char(amount, '99999.99') 
,to_char(negative_amount, '99999.99')
,to_char(amount, '00000.00') 
,to_char(negative_amount, ‘00000.00');

The template should match the definition of the variable and the placement of the decimal separator should match the definition of the decimal. If there are too few decimal places, the number is rounded. If there are too many decimal places, zeros are added.

values 
 to_char(amount, '99999.9') 
,to_char(amount, '99999.999')
,to_char(amount, '9999.99')
,to_char(amount, '999.99');

A dollar sign can be added to the start of the string and a comma may be specified as a group separator. If dollar for currency, comma for group separator, and period for decimal separator do not fit your needs, you can use L for currency symbol character, G for group separator character, and D for decimal point character. The three values are based on information retrieved from message CPX8416 in message file QCPFMSG in the library list. Note that the characters must be in uppercase.

values 
 to_char(amount, '$09,999.99PR')
,to_char(negative_amount, '$09,999.99') 
,to_char(amount, 'L09G999D99')
,to_char(negative_amount, 'L09G999D99');

The minus sign can be placed at the end of the number using MI or negative number can be placed between angle brackets using PR.

values 
 to_char(amount, 'L09G999D99MI')
,to_char(negative_amount, 'L09G999D99MI')
,to_char(amount, 'L09G999D99PR')
,to_char(negative_amount, 'L9999D99PR');

When formatting a date, time, or timestamp, the variable you specify is cast as a timestamp. The format template can use a minus sign, period, slash, comma, apostrophe, semicolon, colon, or blank as a separator. The casing of the special formatting characters is relevant for names but irrelevant for numbers.

Days may be identified using DD for the day of the month, DAY (Day or day) for the day name, and DY (Dy or dy) for the abbreviated day name.

Months may be identified using MM for the month of the year, MONTH (Month or month) for the month name, and MON (Mon or mon) for the abbreviated month name.

A four, three, two or one digit year may be identified using YYYY, YYY, YY and Y.

values 
 to_char(isdate, 'yyyy-mm-dd')  -- *ISO
,to_char(isdate, 'mm-dd-yyyy')  -- *USA
,to_char(isdate, 'Day, dd Month yyyy')
,to_char(isdate, 'Dy, dd Mon yyyy')
,to_char(isdate, 'DY, dd MON yyyy');

Hours may be identified using HH (or HH12) for a 12-hour format or HH24 for a twenty-four hour format.

Minutes may be identified using MI.

AM, A.M., PM, or P.M. may be used as a meridian indicator.

values 
 to_char(stamp, 'hh24:miAM Day, dd Month yyyy')
,to_char(isdate, 'hh24:mi Day, dd Month yyyy');

Other date format elements that you may find useful are J for the Julian date, D for day of week, ID for the ISO day of the week, DDD for day of year, ID for the ISO day of the week, W for week of month, WW for week of the year, IW for the ISO week of the year, Q for quarter, IYYYY (IYYY, IYY, IY) for the ISO year, CC for the century.

When dealing with timestamps you can also use SS for seconds, SSSSS for seconds since previous midnight, MS for Milliseconds, US or NNNNNN for Microseconds.

I hope you find VARCHAR_FORMAT/TOCHAR as useful as I do.