Home » SQL & PL/SQL » SQL & PL/SQL » Sysdate and ToDate( )
Sysdate and ToDate( ) [message #429803] Thu, 05 November 2009 17:59 Go to next message
jtrink
Messages: 8
Registered: November 2009
Junior Member
New to the forums and looking for some help Smile

I would like to know how Oracle determines its ToDate () conversions. For example, If I were to do a four digit julian date conversion '9309' it would convert to 05 - Nov - 2009. Does Oracle base its conversion off of the system's sysdate to not allow a future date? How does it know I don't mean 05-Nov-2019?

Any help would be great!
Re: Sysdate and ToDate( ) [message #429806 is a reply to message #429803] Thu, 05 November 2009 18:21 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
>If I were to do a four digit julian date conversion '9309' it would convert to 05 - Nov - 2009.

Please use CUT & PASTE so we can see this reported "conversion".

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

  1* select to_date(9309,'J') from dual
SQL> /

TO_DATE(9309,'J')
-------------------
4687-06-27 00:00:00

Re: Sysdate and ToDate( ) [message #429807 is a reply to message #429806] Thu, 05 November 2009 18:35 Go to previous messageGo to next message
jtrink
Messages: 8
Registered: November 2009
Junior Member
it would be something like this:

select TO_CHAR(to_date('0001','YDDD'),'MMDDYYYY') from dual

I don't have my source code, but how to do it isn't the premise of my question.
Re: Sysdate and ToDate( ) [message #429808 is a reply to message #429807] Thu, 05 November 2009 19:02 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
>select TO_CHAR(to_date('0001','YDDD'),'MMDDYYYY') from dual
>I don't have my source code, but how to do it isn't the premise of my question.

'9309' using mask 'YDDD' translates to year 2009 & day 309 is 5-NOV

Understand now?
Re: Sysdate and ToDate( ) [message #429810 is a reply to message #429803] Thu, 05 November 2009 19:21 Go to previous messageGo to next message
jtrink
Messages: 8
Registered: November 2009
Junior Member
Yes,

I know what is being converted and how it is being converted.

Say hypothetically the year is 2010 and I ran this conversion:

select TO_CHAR(to_date('2001','YDDD'),'MMDDYYYY')

What year would the date be converted into? 2012 or 2002? That is my question. Will Oracle convert this date to a future date?

[Updated on: Thu, 05 November 2009 19:21]

Report message to a moderator

Re: Sysdate and ToDate( ) [message #429811 is a reply to message #429810] Thu, 05 November 2009 19:34 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
>What year would the date be converted into? 2012 or 2002?
If you want an unambiguous answer, then use unambiguous SQL

  1* select TO_CHAR(to_date('2012001','YYYYDDD'),'MMDDYYYY') from dual
SQL> /

TO_CHAR(
--------
01012012

SQL> select TO_CHAR(to_date('2002001','YYYYDDD'),'MMDDYYYY') from dual;

TO_CHAR(
--------
01012002



By the way '9309' is NOT an Oracle Julian date!


Re: Sysdate and ToDate( ) [message #429817 is a reply to message #429803] Thu, 05 November 2009 20:33 Go to previous messageGo to next message
jtrink
Messages: 8
Registered: November 2009
Junior Member
Well I found the answer to my question. I'll share.

Oracle uses 'sysdate' as a point of reference for the To_Date() function. If you need to convert a 4 digit Julian date e.g. '4302'; Oracle automatically assumes the date occurs in the current decade. Thus making the date '2004' in this example.

If for some reason you need to convert a 4 digit Julian date like '4302' and the sysdate is in year '2010' (the next decade), you will need to use a different point of reference. This is where I am stuck right now...

I don't know why people use 4 digit julian dates in the first place, it just screws everything up! Mad
Re: Sysdate and ToDate( ) [message #429821 is a reply to message #429817] Thu, 05 November 2009 21:10 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
>I don't know why people use 4 digit julian dates in the first place, it just screws everything up!
Y2K revisited!
Look at all the disk space they save by not using 5,6, or 7 digit masks!

Don't blame this on Oracle.
It all the fault of the application architect.
Re: Sysdate and ToDate( ) [message #429822 is a reply to message #429803] Thu, 05 November 2009 21:17 Go to previous message
jtrink
Messages: 8
Registered: November 2009
Junior Member
Yea I know it's not Oracle's fault. I shouldn't have to go through Y2K conversions for 2010!
Previous Topic: PL/SQL writing log during PL/SQL execution
Next Topic: date problem
Goto Forum:
  


Current Time: Mon Dec 05 21:23:00 CST 2016

Total time taken to generate the page: 0.14552 seconds