How do I get the format given by TO_CHAR using TO_DATE ?
Firstly, DATE doesn't have any format. Oracle does not store dates in the format you see. It stores it internally in 7 bytes with each byte storing different components of the datetime value.
Byte Description
---- -------------------------------------------------
1 Century value but before storing it add 100 to it
2 Year and 100 is added to it before storing
3 Month
4 Day of the month
5 Hours but add 1 before storing it
6 Minutes but add 1 before storing it
7 Seconds but add 1 before storing it
The format is only for display purpose. TO_DATE is used to convert a literal into date, and has nothing to do with formatting. To display a date in your desired format, use TO_CHAR with proper FORMAT MODEL.
Also, remember, formatting has an order of precedence:
Let's see the chronological order of precedence, i.e. starting from highest to least:
Using TO_CHAR or TO_DATE at the individual SQL statement
ALTER SESSION SET NLS_DATE_FORMAT=’whatever format model you want’;
Setting it as an OS environment variable on the client machine
Setting of NLS_DATE_FORMAT
is in the database initialization parameters
For example,
Individual SQL statement:
SQL> SELECT HIREDATE, TO_CHAR(hiredate, 'YYYY-MM-DD') FROM emp;
HIREDATE TO_CHAR(HI
------------------- ----------
17/12/1980 00:00:00 1980-12-17
20/02/1981 00:00:00 1981-02-20
22/02/1981 00:00:00 1981-02-22
02/04/1981 00:00:00 1981-04-02
28/09/1981 00:00:00 1981-09-28
01/05/1981 00:00:00 1981-05-01
09/06/1981 00:00:00 1981-06-09
09/12/1982 00:00:00 1982-12-09
17/11/1981 00:00:00 1981-11-17
08/09/1981 00:00:00 1981-09-08
12/01/1983 00:00:00 1983-01-12
03/12/1981 00:00:00 1981-12-03
03/12/1981 00:00:00 1981-12-03
23/01/1982 00:00:00 1982-01-23
14 rows selected.
Session level:
SQL> alter session set nls_date_format='YYYY-MM-DD';
Session altered.
SQL> SELECT hiredate FROM emp;
HIREDATE
----------
1980-12-17
1981-02-20
1981-02-22
1981-04-02
1981-09-28
1981-05-01
1981-06-09
1982-12-09
1981-11-17
1981-09-08
1983-01-12
1981-12-03
1981-12-03
1982-01-23
14 rows selected.
SQL>
TO_DATE((select logical_date
This is wrong.
Never apply TO_DATE on a DATE column. It forces Oracle to:
- first convert it into a string
- then convert it back to date
based on the locale-specific NLS settings. You need TO_DATE to convert a literal into date. For date-arithmetic, leave the date as it is.