Home » SQL & PL/SQL » SQL & PL/SQL » Can someone explain what does this sql statement do?
icon7.gif  Can someone explain what does this sql statement do? [message #226270] Fri, 23 March 2007 02:28 Go to next message
sudharshan
Messages: 48
Registered: November 2006
Member
select to_date('32','j') from dual;

to_date
-------
01-feb-12
Re: Can someone explain what does this sql statement do? [message #226275 is a reply to message #226270] Fri, 23 March 2007 02:39 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Actually, it should be
SELECT TO_DATE(32, 'j') FROM DUAL;
32 is a number, and in this case (with the 'j' format mask) it returns a date value (TO_DATE) from a Julian date whose value is 32.

A clearer example would be
SELECT TO_DATE(2454183, 'j') FROM dual;
which returns today - 23.03.2007.

What is a Julian date? Check the documentation.
Re: Can someone explain what does this sql statement do? [message #226276 is a reply to message #226270] Fri, 23 March 2007 02:41 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
sudharshan wrote on Fri, 23 March 2007 08:28
select to_date('32','j') from dual;

to_date
-------
01-feb-12
You select a julian date:
SQL> select to_date('32', 'j') x
  2  from   dual
  3  /

X
---------
01-FEB-12

SQL> alter session set nls_date_format = 'month ddth yyyy AD'
  2  /

Session altered.

SQL> select to_date('32', 'j') x
  2  from   dual
  3  /

X
----------------------
february  01st 4712 BC


from the SQL reference
J -> Julian day; the number of days since January 1, 4712 BC. Number specified with 'J' must be integers.
It's a bit confusing actually, because Julian day 1 = Januari first. Add an extra 31 days and you come to Februari first.

MHE
Re: Can someone explain what does this sql statement do? [message #226278 is a reply to message #226270] Fri, 23 March 2007 02:45 Go to previous messageGo to next message
sudharshan
Messages: 48
Registered: November 2006
Member
Thank you very much
Re: Can someone explain what does this sql statement do? [message #226279 is a reply to message #226270] Fri, 23 March 2007 02:46 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Julian day; the number of days since January 1, 4712 BC. Number specified with J must be integers.

Apologies i didn't see the earlier Post.

[Updated on: Fri, 23 March 2007 02:46]

Report message to a moderator

Previous Topic: ORA-00600: internal error code,
Next Topic: execution of pl/sql code, 9i versus 10g
Goto Forum:
  


Current Time: Sat Feb 08 13:41:19 CST 2025