Home » SQL & PL/SQL » SQL & PL/SQL » Oracle DATE Problem (Oracle 9i)
Oracle DATE Problem [message #430888] Thu, 12 November 2009 21:57 Go to next message
mohan_krishnan83
Messages: 10
Registered: July 2006
Junior Member
When I Try to convert a Date field to char using to_char(date,'dd/mm/yyyy') I am getting result in non-centuary year format.

Database is Oracle 9i release 2
Database Nls settings are as follows

NLS_DATE_FORMAT
Value -Null
ISDefault -True

NLS_DATE_LANGUAGE
Value - Null
IS Default -True

Eg:

select
to_char(approved_key_date,'dd/mm/yyyy')
,to_char(proposed_key_date,'dd/mm/yyyy')
from cms_sf_events
where style_file_seq_num ='628645';

For the Above Query,I am getting result as follows

12/12/0009 12/12/2009
28/03/2009
28/03/2009 28/11/2009
28/02/2009
28/03/2009
28/03/2009
12/12/0009 12/12/0009
12/12/0009 12/12/0009
21/11/0009 21/11/0009
21/11/0009 21/11/0009
12/12/0009 12/12/0009

Any Help In this regard much appreciated.


Re: Oracle DATE Problem [message #430889 is a reply to message #430888] Thu, 12 November 2009 22:03 Go to previous messageGo to next message
glakshkar
Messages: 7
Registered: September 2005
Location: PUNE
Junior Member
Please user TO_DATE(date,'DD/MM/RRRR') instead of TO_DATE(date,'DD/MM/YYYY')

RRRR - takes care of centrury
Re: Oracle DATE Problem [message #430892 is a reply to message #430889] Thu, 12 November 2009 22:47 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
glakshkar wrote on Thu, 12 November 2009 20:03
Please user TO_DATE(date,'DD/MM/RRRR') instead of TO_DATE(date,'DD/MM/YYYY')

RRRR - takes care of centrury



RRRR - takes care of centrury

glakshkar,
Please provide reproducible test case which validates your response.

Re: Oracle DATE Problem [message #430893 is a reply to message #430888] Thu, 12 November 2009 22:52 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
>I am getting result in non-centuary year format.

If what you posted reflects reality, Then I conclude the data in the table is not what you expect it to be.

I believe Oracle returned what exists within table.
Re: Oracle DATE Problem [message #430895 is a reply to message #430893] Thu, 12 November 2009 23:28 Go to previous messageGo to next message
glakshkar
Messages: 7
Registered: September 2005
Location: PUNE
Junior Member
Dear blackSwan,

Just execute these 2 query on SCOTT schema. 1st query will not return you the result(because oracle converts this date to 0081) but second will because oracle interenally converted year to 1981.


select * from emp where hiredate = to_date('01/05/81','dd/mm/yy');
select * from emp where hiredate = to_date('01/05/81','dd/mm/rr');

This was just to show you the difference between the two.

However, in asked question,

i agree with you about the inconsistent data, due to which data is coming in such format.

Re: Oracle DATE Problem [message #430896 is a reply to message #430895] Thu, 12 November 2009 23:35 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
RRRR - takes care of centrury

glakshkar,
Please provide reproducible test case which validates your response or admit you were not correct.
Re: Oracle DATE Problem [message #430906 is a reply to message #430889] Fri, 13 November 2009 00:46 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
glakshkar wrote on Fri, 13 November 2009 05:03
Please user TO_DATE(date,'DD/MM/RRRR') instead of TO_DATE(date,'DD/MM/YYYY')

RRRR - takes care of centrury

Original post is about to_char, not about to_date, so your point is not valid.
Re: Oracle DATE Problem [message #430969 is a reply to message #430893] Fri, 13 November 2009 08:10 Go to previous messageGo to next message
joy_division
Messages: 4617
Registered: February 2005
Location: East Coast USA
Senior Member
BlackSwan wrote on Thu, 12 November 2009 23:52
>I am getting result in non-centuary year format.

If what you posted reflects reality, Then I conclude the data in the table is not what you expect it to be.

I believe Oracle returned what exists within table.


BlackSwan is correct. But the reason for this is that the data was input using only a 2-digit year (possibly with an Oracle Form).

Remember, years are 4 digits (or at least they have been for about 1010 years), not 2 digits.
Re: Oracle DATE Problem [message #431133 is a reply to message #430888] Sun, 15 November 2009 16:28 Go to previous message
wakula
Messages: 150
Registered: February 2008
Location: Poland
Senior Member
Please, read about ISO-8601. There you would find several possible causes - although the document/standard is non-Oracle.
My guess is that you have used 2-digit year (as pointed out by joy_division) and it was converted in some weird way.
Previous Topic: how to rewrite the query the sub query.
Next Topic: inserting predecessor and succerror values
Goto Forum:
  


Current Time: Fri Sep 30 02:12:56 CDT 2016

Total time taken to generate the page: 0.13501 seconds