Binaryworld - Displaying Date-Time in different formats in SQL ... [ ORACLE -> Other ]

The TO_CHAR function, when applied to dates can be used to display a date-time in a user-defined format. This article will give you an introduction to the possibilities.

The following date format elements can be used together as shown in the examples to display date in a custom manner.

Examples:

Click here to copy the following block

SELECT To_Char(SYSDATE ,'"The date is" Dd Month, "Year" YYYY AD.') FROM dual;
--Output:
--The date is 15 January , Year 2005 AD.

SELECT To_Char(SYSDATE ,'"The time is" HH:MI AM') FROM dual;
--Output:
--The time is 02:41 PM

SELECT To_Char(SYSDATE ,'"Today is" DD-MM-YYYY, "This is the" Qspth "Quarter of the year."') FROM dual;
--Today is 15-07-2005, This is the Third Quarter of the year.

SELECT To_Char(SYSDATE ,'Ddth') FROM dual;
--Output:
--15th

Date Format Elements and their description:

Click here to copy the following block

Element       Description

"Quoted Text"    All double-quoted text is reproduced in the result string.
Punctuation     All punctuation symbols are reproduced in the result string.

AD, A.D.      Indicates AD or BC, depending on the year with/without periods
AM, A.M.      AM/PM indicator with/without periods
BC, B.C.      AD/BC indicator with/without periods

CC, SCC       Century, in case of SCC, BC dates are prefixed with a ‘-‘

D, DD, DDD     Day of the week (1-7), month (1-31) or year (1-366) respectively
DAY         Name of the day (blank padded to length of nine characters).
DY         Name of day in short

I, IY, IYY, IYYY  One, two three or four digit year based on the ISO standard

Q          Quarter of the year

RM         Month in Roman Numerals
WW, W        Week of the year or month

MM         Month in 2 digits
MON         Name of Month in 3 letter abbreviated form.
MONTH        Name of Month spelled out

YYYY, SYYYY     Year. In case of SYYYY, BC dates are prefixed with a ‘-‘

Special Modifiers:

Element       Description
TH         Ordinal Number (e.g. DDTH will show 3 as 3RD)
          Also note: Ddth will show 3 as 3rd

SP         Number spelled out (e.g. DDSP will show 3 as THREE)
          Also note: Ddsp will show 3 as Three.

SPTH or THSP    Spelt out ordinal numbers (DDSPTH will show 3 as THIRD)
          As in above examples, changing case changes output:
          Ddspth will show 3 as Third.