4.8 Date-Time Format Strings

The TO_CHAR scalar function supports a variety of format strings to control the output of date and time values. The format strings consist of keywords that SQL interprets and replaces with formatted values.

The format strings are case sensitive. For instance, SQL replaces 'DAY' with all uppercase letters, but follows the case of 'Day'.

Supply the format strings, enclosed in single quotation marks, as the second argument to the TO_CHAR function. For example:

SELECT C1 FROM T2;
C1
--
09/29/1952
1 record selected
SELECT TO_CHAR(C1, 'Day, Month ddth'),
         TO_CHAR(C2, 'HH12 a.m.') FROM T2;
TO_CHAR(C1,DAY, MONTH DDTH)  TO_CHAR(C2,HH12 A.M.)
---------------------------  ---------------------
Monday   , September 29th    02 p.m.
1 record selected

For details of the TO_CHAR function, see "TO_CHAR" on page 4-96.

4.8.1 Date Format Strings

A date format string can contain any of the following format keywords along with other characters. The format keywords in the format string are replaced by corresponding values to get the result. The other characters are displayed as literals.

CC

The century as a 2-digit number.

YYYY

The year as a 4-digit number.

YYY

The last 3 digits of the year.

YY

The last 2 digits of the year.

Y

The last digit of the year.

Y,YYY

The year as a 4-digit number with a comma after the first digit.

Q

The quarter of the year as 1-digit number (with values 1, 2, 3, or 4).

MM

The month value as 2-digit number (in the range 01-12).

MONTH

The name of the month as a string of 9 characters ('JANUARY' to 'DECEMBER ').

MON

The first 3 characters of the name of the month (in the range 'JAN' to 'DEC').

WW

The week of year as a 2-digit number (in the range 01-52).

W

The week of month as a 1-digit number (in the range 1-5).

DDD

The day of year as a 3-digit number (in the range 001-365).

DD

The day of month as a 2-digit number (in the range 01-31).

D

The day of week as a 1-digit number (in the range 1-7, 1 for Sunday and 7 for Saturday).

DAY

The day of week as a 9 character string (in the range 'SUNDAY' to 'SATURDAY '.

DY

The day of week as a 3 character string (in the range 'SUN' to 'SAT').

J

The Julian day (number of days since DEC 31, 1899) as an 8 digit number.

TH

When added to a format keyword that results in a number, this format keyword ('TH') is replaced by the string 'ST', 'ND', 'RD' or 'TH' depending on the last digit of the number.

Example

SELECT C1 FROM T2;
C1
--
09/29/1952
1 record selected
SELECT TO_CHAR(C1, 'Day, Month ddth'),
         TO_CHAR(C2, 'HH12 a.m.') FROM T2;
TO_CHAR(C1,DAY, MONTH DDTH)  TO_CHAR(C2,HH12 A.M.)
---------------------------  ---------------------
Monday   , September 29th    02 p.m.
1 record selected

4.8.2 Time Format Strings

A time format string can contain any of the following format keywords along with other characters. The format keywords in the format string are replaced by corresponding values to get the result. The other characters are displayed as literals.

AM The string 'AM' or 'PM' depending on whether time

PM

corresponds to forenoon or afternoon.

A.M. P.M.

The string 'A.M.' or 'P.M.' depending on whether time corresponds to forenoon or afternoon.

HH12

The hour value as a 2-digit number (in the range 00 to 11).

HH HH24

The hour value as a 2-digit number (in the range 00 to 23).

MI

The minute value as a 2-digit number (in the range 00 to 59).

SS

The seconds value as a 2-digit number (in the range 00 to 59).

SSSSS

The seconds from midnight as a 5-digit number (in the range 00000 to 86399).

MLS

The milliseconds value as a 3-digit number (in the range 000 to 999).

Example

SELECT C1 FROM T2;
C1
--
09/29/1952
1 record selected
SELECT TO_CHAR(C1, 'Day, Month ddth'),
         TO_CHAR(C2, 'HH12 a.m.') FROM T2;
TO_CHAR(C1,DAY, MONTH DDTH)  TO_CHAR(C2,HH12 A.M.)
---------------------------  ---------------------
Monday   , September 29th    02 p.m.
1 record selected