Template patterns for date/time conversions

IBM® Netezza® SQL recognizes certain patterns in the output template and replaces them with the appropriately formatted data.

Table 1. Template patterns for date/time conversions
Pattern Description Example
HH or HH12 Hour of day (01-12). to_char(timestamp '2015-02-14 20:19:07','HH') returns '08'
HH24 Hour of day (00-23). to_char(timestamp '2015-02-14 20:19:07', 'HH24') returns '20'
MI Minute (00-59). to_char(timestamp '2015-02-14 20:19:07','MI') returns '19'
SS Second (00-59). to_char(timestamp '2015-02-14 20:19:07','SS') returns '07'
SSSS Seconds past midnight (0-86399). to_char(timestamp '2015-02-14 20:19:07', 'SSSS') returns '73147'
MS Milliseconds (00-00.999) after rounding. to_char(timestamp '2015-02-14 20:19:07.123456789', 'MS') returns '123'
US Microseconds (00-00.999999) after rounding. to_char(timestamp '2015-02-14 20:19:07.123456789', 'US') returns '123457' 
A.M.  P.M.
a.m.  p.m.
AM    PM
am    pm
 Meridian indicator (uppercase or lowercase, with or without periods). The meridian indicator specified by the template is adjusted in the output to correspond to the hour (am for hours from 0-12 or pm for hours from 12-23). The format of the meridian indicator specified by the template (uppercase or lowercase, with or without periods) is used for the output. to_char(timestamp '2015-02-14 20:19:07', 'HH:MI:SS am') returns '08:19:07 pm'
Y,YYY Year (4 and more digits) with a comma. to_char(date '2016-02-14','Y,YYY') returns '2,016'
YYYY Year (4 and more digits). to_char(date '2016-02-14','YYYY') returns '2016'
YYY Last 3 digits of the year. to_char(date '2016-02-14','YYY') returns '016'
YY Last 2 digits of the year. to_char(date '2016-02-14','YY') returns '16'
Y Last digit of the year. to_char(date '2016-02-14','Y') returns '6'
IYYY ISO year (4 and more digits). to_char(date '2016-01-01','IYYY') returns '2015'
IYY Last 3 digits of the ISO year. to_char(date '2016-02-14','IYY') returns '015'
IY Last 2 digits of the ISO year. to_char(date '2016-02-14','IY') returns '15'
I Last digits of ISO year. to_char(date '2016-02-14','I') returns '5' 
B.C.  A.D.
b.c.  a.d.
BC    AD
bc    ad
 Era indicator (uppercase or lowercase, with or without periods). The format of the era indicator specified by the template (uppercase or lowercase, with or without periods) is used for the output. to_char(date '2016-02-14','YYYY B.C.') returns '2016 A.D.'
MONTH Full uppercase month name, padded with trailing blanks to 9 characters. to_char(date '2016-02-14','MONTH') returns 'FEBRUARY '
Month Full mixed case month name, padded with trailing blanks to 9 characters. to_char(date '2016-03-14','Month') returns 'March '
month Full lowercase month name, padded with trailing blanks to 9 characters. to_char(date '2016-04-14','month') returns 'april '
MON Abbreviated uppercase month name (3 chars). to_char(date '2016-02-14','MON') returns 'FEB'
Mon Abbreviated mixed case month name (3 chars). to_char(date '2016-03-14','Mon') returns 'Mar'
mon Abbreviated lowercase month name (3 chars). to_char(date '2016-04-14','mon') returns 'apr'
MM Month number (01-12). to_char(date '2016-04-14','MM') returns '04'
DAY Full uppercase day name, padded with trailing blanks to 9 characters. to_char(date '2016-02-15','DAY') returns 'MONDAY '
Day Full mixed case day name, padded with trailing blanks to 9 characters. to_char(date '2016-02-16','Day') returns 'Tuesday '
day Full lowercase day name, padded with trailing blanks to 9 characters. to_char(date '2016-02-17','day') returns 'wednesday'
DY Abbreviated uppercase day name (3 chars). to_char(date '2016-02-15','DY') returns 'MON'
Dy Abbreviated mixed case day name (3 chars). to_char(date '2016-02-16','Dy') returns 'Tue'
dy Abbreviated lowercase day name (3 chars). to_char(date '2016-02-17','dy') returns 'wed'
DDD Day of the year (001-366). to_char(date '2016-02-17','DDD') returns '048'
DD Day of the month (01-31). to_char(date '2016-02-17','DD') returns '17'
D Day of the week (1-7; Sunday=1). to_char(date '2016-02-17','D') returns '4'
W Week of the month (1-5), where the first week starts on the first day of the month. to_char(date '2016-02-17','W') returns '3'
WW Week number of the year (01-53), where the first week starts on the first day of the year. to_char(date '2016-02-17','WW') returns '07'
IW ISO week number of the year. The first Thursday of the new year is in week 1. to_char(date '2016-01-01','IW') returns '53'
CC Century (2 digits). to_char(date '2016-02-17','CC') returns '21'
J Julian days, that is, the number of days since November 24, 4714 BC. to_char(date '2016-02-17','J') returns '2457389'
Q Quarter to_char(date '2016-02-17','J') returns '1'
RM Month in uppercase Roman Numerals (I-XII; I=January), padded with trailing blanks to 4 characters. to_char(date '2016-04-11','RM') returns 'IV '
rm Month in lowercase Roman Numerals (i-xii; i=January), padded with trailing blanks to 4 characters. to_char(date '2016-10-11','RM') returns 'X '
Usage notes:
  • Spaces in a template are reflected in the output. For example, to_char(timestamp '2015-02-14 20:19:07','HH MI SS') returns '08 19 07'.
  • To output text, place it within double quotation marks. For example, to_char(date '2016-04-02','"Year: "YYYY') returns 'Year: 2016'.
  • To output a double quotation mark, precede it with a backslash. For example, to_char(date '2015-02-14','\"YYYY\" \"FMMonth\"') returns '"2015" "February"'.
You can apply the following modifiers to any template pattern to alter its behavior.
Table 2. Template modifiers
Modifier Description
FM (prefix) Suppresses leading zeros and trailing blanks that would otherwise be added to make the output a fixed width. For example:

to_char(date '2016-03-14','FMMonth') returns 'March'

The FM prefix suppresses the four trailing blanks that normally would follow 'March'.
FX (prefix) Forces an input string to contain at least as many blanks as are specified in the template:
  • When the FX prefix is not specified, the input string can contain the same number as, more, or fewer blanks than the template. For example:
    • to_date('31 Dec 2015','DD Mon YYYY') returns 2015-12-31
    • to_date('31 Dec 2015','DD Mon YYYY') returns 2015-12-31
    • to_date('31 Dec 2015','DD Mon YYYY') returns 2015-12-31
  • When the FX prefix is specified, the input string can contain the same number as, more, but not fewer blanks than the template. For example:
    • to_date('31 Dec 2015','FXDD Mon YYYY') returns 2015-12-31
    • to_date('31 Dec 2015','FXDD Mon YYYY') returns 2015-12-31
    • to_date('31 Dec 2015','FXDD Mon YYYY') returns ERROR: Invalid Date.
This can be helpful when a stored procedure needs to verify that character input is properly formatted before processing it.
TH (suffix) Appends the appropriate uppercase ordinal-number suffix (ST, ND, RD, or TH). For example:

to_char(date '2016-04-01','FMDDTH') returns '1ST'
th (suffix) Appends the appropriate lowercase ordinal-number suffix (st, nd, rd, or th). For example:

to_char(date '2016-04-02','FMDDth') returns '2nd'
The following table describes the template patterns for numeric conversions.
Table 3. Template patterns for numeric conversions
Pattern Description Example
9 Indicates a position for a digit. A blank is included in the output for each 9 in the template that does not correspond to a digit in the output.
to_char(-125,'999') returns '-125'
to_char(-125,'99999') returns '  -125'
to_char(125,'999') returns ' 125'
0 Indicates a position for a leading zero. to_char(-125,'00999') returns '-00125'
. A period indicates a position for a decimal point. to_char(-125,'999.999') returns '-125.000'
, A comma indicates a position for a group (thousand) delimiter. Which delimiter is used depends on your locale.  
PR This suffix causes a negative value to be displayed in angle brackets. Does not affect a positive value. to_char(-485,'999PR') returns '<485>'
S This prefix or suffix places a plus (+) or minus (-) sign in the corresponding position. to_char(485,'S999') returns '+485'
L Currency symbol. Which symbol is used depends on your locale.  
D Decimal delimiter. Which delimiter is used depends on your locale. to_char(-125,'999D99') returns '-125.00'
G Group separator (uses locale). to_char(-3125,'9G999D9') returns '-3125.0'
MI This prefix or suffix:
  • For a negative number, places a minus (-) sign in the corresponding position.
  • For a positive number, places a blank in the corresponding position.
A sign that is formatted by using the MI pattern is not an anchor in the number; for example:
  • to_char(-12,'S9999') returns ' -12'.
  • to_char(-12,'MI9999') returns '- 12'.
to_char(-485,'MI999') returns '-485'
to_char(-485,'999MI') returns '485-'
to_char(485,'MI999') returns '  485'
to_char(485,'999MI') returns ' 485 '
RN Roman numeral (input 1 - 3999). Roman numerals ignore decimal fractions. to_char(5.2,'RN') returns 'V'
V Shifts the output by one power of 10 for each 9 to the right of the V. For a to_char function, a template cannot contain both a V pattern and a decimal point. For example, 99.9V99 or 99D9V99 is not allowed.
to_char(12.4,'99V999') returns ' 12400'
to_char(12.45,'99V9') returns ' 125'