Template patterns for date/time conversions
IBM® Netezza® SQL recognizes certain patterns in the output template and replaces them with the appropriately formatted data.
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' |
|
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' |
|
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.
Modifier | Description |
---|---|
FM (prefix) |
Suppresses leading zeros and trailing blanks that would otherwise
be added to make the output a fixed width. For example:
The |
FX (prefix) |
Forces an input string to contain at least as
many blanks as are specified in the template:
|
TH (suffix) |
Appends the appropriate uppercase ordinal-number suffix (ST,
ND, RD, or TH). For example:
|
th (suffix) |
Appends the appropriate lowercase ordinal-number suffix (st,
nd, rd, or th). For example:
|
The following table describes the 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:
|
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' |