TO_DATE function in Oracle is the most useful as well as ‘annoying’ function.
TO_DATE function converts string into date data types.
Eg :-
select to_date(‘2016/11/13′,’yyyy/mm/dd’) from dual;
returns 2016/11/13
Note that Oracle converts string ‘2016/11/13’into Date ‘2016/11/13’. Now this value can be inserted into a table having a column date data type.
Note :- Before doing any testing on date columns make sure the default date format settings
For example in my sqldeveloper tool, go to Tools–> Preferences –> Database –> NLS
Below sql should have returned 2016/11/13, but shows 13-NOV-16 which is in sqldeveloper nls date format. This is the most common mistake developers make.
Alternatively you can use below sql also to change the date format.
alter session set nls_date_format = ‘dd-mon-yyyy’;
The above SQL can be used from SQLPLUS also.
If you do not want to change the nls settings, you can use TO_CHAR function also in required format, see below example.
select to_char(to_date(‘2016/11/13′,’yyyy/mm/dd’),’yyyy/mm/dd’) from dual;
(TO_CHAR and TO_DATE)
Important :- Oracle stores date data type with time.
Below are the most used formats with TO_DATE
| Format | Description | Example | Value |
| AD | AD or BC | to_char(to_date(‘2016/11/13 AD’,’yyyy/mm/dd AD’),’AD’) | AD |
| BC | AD or BC | to_char(to_date(‘2016/11/13 BC’,’yyyy/mm/dd AD’),’AD’) | BC |
| D | Day of week (1 7) | to_char(to_date(‘2016/11/13′,’yyyy/mm/dd’),’D’) | 1 |
| DAY | Name of day | to_char(to_date(‘2016/11/13′,’yyyy/mm/dd’),’DAY’) | SUNDAY |
| DD | Day of month (1 31) | to_char(to_date(‘2016/11/13′,’yyyy/mm/dd’),’DD’) | 13 |
| DDD | Day of year (1 366) | to_char(to_date(‘2016/11/13′,’yyyy/mm/dd’),’DDD’) | 318 |
| DY | Abbr name of day | to_char(to_date(‘2016/11/13′,’yyyy/mm/dd’),’DY’) | SUN |
| HH | Hour of day (1 12) | to_char(to_date(‘2016/11/13 01:12:35′,’yyyy/mm/dd hh:mi:ss’),’HH’) | ‘01’ |
| HH12 | Hour of day (1 12) | to_char(to_date(‘2016/11/13 04:12:35 PM’,’yyyy/mm/dd hh:mi:ss PM’),’HH12′) | ‘04’ |
| HH24 | Hour of day (0 23) | to_char(to_date(‘2016/11/13 04:12:35 PM’,’yyyy/mm/dd hh:mi:ss PM’),’HH24′) | 16 |
| IW | Week of year | to_char(to_date(‘2016/11/13′,’yyyy/mm/dd’),’IW’) | 45 |
| J | Julian day | to_char(to_date(‘2016/11/13′,’yyyy/mm/dd’),’J’) | 2457706 |
| MI | Minute (0 59) | to_char(to_date(‘2016/11/13 01:12:35′,’yyyy/mm/dd hh:mi:ss’),’MI’) | 12 |
| MM | Month (01 etc) | to_char(to_date(‘2016/11/13 01:12:35′,’yyyy/mm/dd hh:mi:ss’),’MM’) | 11 |
| MON | Abbr month | to_char(to_date(‘2016/11/13 01:12:35′,’yyyy/mm/dd hh:mi:ss’),’MON’) | NOV |
| MONTH | Name of month | to_char(to_date(‘2016/11/13 01:12:35′,’yyyy/mm/dd hh:mi:ss’),’MONTH’) | NOVEMBER |
| AM | AM or PM | to_char(to_date(‘2016/11/13 01:12:35 AM’,’yyyy/mm/dd hh:mi:ss AM’),’AM’) | AM |
| PM | AM or PM | to_char(to_date(‘2016/11/13 01:12:35 PM’,’yyyy/mm/dd hh:mi:ss AM’),’AM’) | PM |
| Q | Quarter of year | to_char(to_date(‘2016/11/13 01:12:35 AM’,’yyyy/mm/dd hh:mi:ss AM’),’Q’) | 4 |
| RRRR | Round year | to_char(to_date(‘2016/11/13 01:12:35 AM’,’yyyy/mm/dd hh:mi:ss AM’),’RRRR’) | 2016 |
| SS | Second (0 59) | to_char(to_date(‘2016/11/13 01:12:35 AM’,’yyyy/mm/dd hh:mi:ss AM’),’SS’) | 35 |
| WW | Week of year (1 53) | to_char(to_date(‘2016/11/13 01:12:35 AM’,’yyyy/mm/dd hh:mi:ss AM’),’WW’) | 46 |
| W | Week of month (1 5) | to_char(to_date(‘2016/11/13 01:12:35 AM’,’yyyy/mm/dd hh:mi:ss AM’),’W’) | 2 |
| YYYY | Last 4 digit(s) of year | to_char(to_date(‘2016/11/13 01:12:35 AM’,’yyyy/mm/dd hh:mi:ss AM’),’YYYY’) | 2016 |
| YY | Last 2 digit(s) of year | to_char(to_date(‘2016/11/13 01:12:35 AM’,’yyyy/mm/dd hh:mi:ss AM’),’YY’) | ‘16’ |
Always check below before you start using TO_DATE function
- NLS_DATE_FORMAT ( SELECT sys_context (‘userenv’,’NLS_DATE_FORMAT’) FROM DUAL )
or
2. use TO_CHAR for formating
Also read TO_CHAR Function
