Date Formats

DATE FORMATS

 

A note about the available date formats:

Element Description Note
D Day of Week Based on NLS_TERRITORY(!)
SQL> alter session set nls_territory=AMERICA;

Session altered.


SQL> select to_char(to_date('28-08-2017','dd-mm-yyyy'),'D') from dual;

T

-

2



SQL> alter session set nls_territory="The Netherlands";

Session altered.



SQL> select to_char(to_date('28-08-2017','dd-mm-yyyy'),'D') from dual;

T

-

1
DAY Name of Day Case sensitive DAY, Day or day. Based on NLS_DATE_LANGUAGE.
DD Day of Month
DDD Day of Year
DY Abbreviated day Case sensitive, DY, Dy or dy. Based on NLS_DATE_LANGUAGE
MM Number of month JAN=1, DEC=12
MON Abbreviated month Case sensitive, MON, Mon or mon. Based on NLS_DATE_LANGUAGE
MONTH Name of month Case sensitive, MONTH, Month or month. Based on NLS_DATE_LANGUAGE
RM Roman month JAN=I, DEC=XII. Case sensitive RM, Rm or rm.
W Week of Month On date. 1st week starts on 1st,2nd week starts on 8th.
WW Week of Year On date. 1st week starts on 1st,2nd week on 8th day of Year.
IW Week of Year (ISO) ISO week starts Monday. Week 1 is the week including the 1st Thursday
Q Quarter of year
CC Century First 2 digits of year +1 except for millenia
SQL> select to_char(to_date('1-1-2001','dd-mm-yyyy'),'CC') from dual;

TO

--

21



SQL> select to_char(to_date('1-1-2000','dd-mm-yyyy'),'CC') from dual;

TO

--

20
SCC Century with – for BC Prefix is space for AD, – for BC.
YYYY Year
SYYYY Year with – for BC Prefix is space for AD, – for BC.
Y

YY

YYY

Last 1, 2 or 3 digits of year
YEAR

SYEAR

Full name of year Case sensitive YEAR, Year or year. Only in English
SQL> select to_char(to_date('1-1-2017','dd-mm-yyyy'),'Year') from dual;



TO_CHAR(TO_DATE(

----------------

Twenty Seventeen



SQL> select to_char(to_date('31-dec-1BC','dd-mon-yyyyAD')+1,'Syear') from dual;



TO_CHAR(TO_DATE('31-DEC-1BC','DD-MON-YYYYAD

-------------------------------------------

0000000000000000000000000000000000000000000
Y,YYY Year with grouping separator Only available on the second position. ‘,’ translates to nls_numeric_characters grouping separator.
IY

IYY

IYYY

IYYYY

Year (ISO) ISO Year number. The ISO year starts on Monday, based on the 1st Thursday of the year.
AD/A.D.

BC/B.C.

Indicator for BC/AD Case sensitive, language sensitive based on NLS_DATE_LANGUAGE.
HH

HH12

Hour in 12:00 format
HH24 Hour in 24:00 format
MI Minutes
SS Seconds
SSSSS Seconds from start of day Be careful to put 5 S’s
SQL> select to_char(to_date('13:45:26','HH24:MI:SS'),'SSSSS') from dual;



TO_CH

-----

49526



SQL> select to_char(to_date('13:45:26','HH24:MI:SS'),'SSSS') from dual;



TO_C

----

2626
AM/A.M.

PM/P.M.

AM/PM indicator Case sensite AM/am. Depending on NLS_DATE_LANGUAGE.
J Julian Date
Suffixes:
TH Ordinal Number Can be used with any numeric element
SQL> select to_char(to_date('14-7-2017','dd-mm-yyyy'),'DDTH-mmTH-yyyyth') from dual;



TO_CHAR(TO_DATE(

----------------

14TH-07th-2017th
SP Spelled Number
SQL> select to_char(to_date('14-7-2017','dd-mm-yyyy'),'DDSP-MmSP-yyyysp') from dual;



TO_CHAR(TO_DATE('14-7-2017','DD-MM-YY

-------------------------------------

FOURTEEN-Seven-two thousand seventeen

 

Leave a Reply

Your email address will not be published. Required fields are marked *