- /
- /
- /
- /
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 |
||