SELECT TO_CHAR((select logical_date -1 from logical_date
where logical_date_type='B'),'DD/MM/YYYY HH24:MI:SS') FROM DUAL;
This Query returns 23/04/2016 00:00:00
o/p of select logical_date -1 from logical_date where logical_date_type='B' :4/23/2016
SELECT TO_DATE((select logical_date -1 from logical_date
where logical_date_type='B'),'DD/MM/YYYY HH24:MI:SS') FROM DUAL;
This Query returns 4/23/0016.
How do I get the format given by TO_CHAR using TO_DATE ??
Dates do not have any intrinsic format. Oracle has an internal representation which doesn't look like anything you'd recognise as a date. The client or application decides how to display the date (e.g. using NLS_DATE_FORMAT), or you can use TO_CHAR to convert the date to a string with a format you specify, as you're doing in your first example.
In your second example you are doing an implicit comversion of your actual date to a string, using your NLS setting - losing the century in the process, so presumably that has YY rather than YYYY - and then you convert that back to a date. I'm surprised that doesn't error as you are swapping the month and day positions.
Do not do that. Converting to a string and back to a date is pointless, even if your settings don't lose information on the way.
If you want it as a date for a client or other process to use just do:
select logical_date -1
from logical_date
where logical_date_type='B'
If you want to compare against another date then still leave it as a date:
select other_columns
from logical_date
where logical_date_type='B'
and logical_date -1 < sysdate
If you want to specify the format for display then use:
select to_char(logical_date -1, 'DD/MM/YYYY')
from logical_date
where logical_date_type='B'
The problem is related to the default date format for your session that is configured in your oracle client settings To check the NLS_DATE_FORMAT settings for your Session
SELECT value
FROM nls_session_parameters
WHERE parameter = 'NLS_DATE_FORMAT'
here's how you can change this setting for your session to achieve the desired results:
alter session set nls_date_format='DD/MM/YYYY HH24:MI:SS';
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:
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.
