Home » SQL & PL/SQL » SQL & PL/SQL » Julian date Convesion
icon8.gif  Julian date Convesion [message #299489] Tue, 12 February 2008 03:52 Go to next message
makinanag
Messages: 1
Registered: February 2008
Junior Member
Hi there folks

I have this julian number 39490.4281134259 which is generated by our system imported from Australia... This Julian Date transaction was created on 12/02/2008 10:16:29, so when converting it I was expecting to get the same date... I used this following select statement to convert this

SELECT to_date(to_char(39490), 'J')
FROM dual;
But this statement isnstead of returning that date it gives me 13/FEB/05 and when I TO_CHAR the results I get

SELECT to_char(to_date(to_char(39490), 'J'),'DD/MM/YYYY')
FROM dual;

I get 13/02/4604

PLEASE HELP ME GUYS ON HOW TO CONVERT THIS TO MATCH THE 12/02/2008

[mod-edit] removed font (very hard to read with my browser settings).

[Updated on: Tue, 12 February 2008 07:53] by Moderator

Report message to a moderator

Re: Julian date Convesion [message #299494 is a reply to message #299489] Tue, 12 February 2008 03:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select to_char(to_date('12/02/2008','DD/MM/YYYY'),'J') from dual;
TO_CHAR
-------
2454509

1 row selected.

Julian number for '12/02/2008' is 2454509 not 39490 which is:
SQL> SELECT to_char(to_date(to_char(39490), 'J'),'DD/MM/SYYYY') from dual;
TO_CHAR(TO_
-----------
13/02/-4604

1 row selected.

Your starting point is:
SQL> select to_date('12/02/2008','DD/MM/YYYY')-39490+1 from dual;
TO_DATE('12/02/2008
-------------------
31/12/1899 00:00:00

1 row selected.

Regards
Michel


Re: Julian date Convesion [message #299499 is a reply to message #299489] Tue, 12 February 2008 04:22 Go to previous message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
very similar question

[Updated on: Tue, 12 February 2008 04:22]

Report message to a moderator

Previous Topic: Running Total
Next Topic: Generate the file having dynamic columns
Goto Forum:
  


Current Time: Mon Dec 05 07:05:27 CST 2016

Total time taken to generate the page: 0.16375 seconds