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