Home » SQL & PL/SQL » SQL & PL/SQL » Convertion of Julian Date into dd/mm/yyyy (Oracle 10g)
Convertion of Julian Date into dd/mm/yyyy [message #446421] Mon, 08 March 2010 05:18 Go to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Hi,
I have been given some data in excel sheet to be uploaded in an Oracle Table. The dates are in Julian. The date in Julian in excel sheet is as :-'110048'.

In the excel file, I found that the cell was formatted as General and when I changed the formatting to Date I got the result as '19/04/2211'.

Please tell me a way to convert this Julian to mm/dd/yyyy format to be inserted into a table in Oracle.

Tried this :-

SQL> SELECT to_char(to_date(to_char(110048), 'J'),'DD/MM/YYYY') FROM dual;

TO_CHAR(TO
----------
18/04/4411


Not sure how to go about it.


Regards,
Mahi

[Updated on: Mon, 08 March 2010 05:20]

Report message to a moderator

Re: Convertion of Julian Date into dd/mm/yyyy [message #446424 is a reply to message #446421] Mon, 08 March 2010 05:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Excel does NOT use Julian date.
Have a look at your Excel documentation to know what is the origin (it depends on your Excel version).

Regards
Michel
Re: Convertion of Julian Date into dd/mm/yyyy [message #446428 is a reply to message #446421] Mon, 08 March 2010 05:38 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Your method is correct, the value you're getting from excel is wrong (or at least not a julian date as far as oracle is concerned).

Julian dates are a number of days since a reference date - which is 01-01-4712 years BCE according to the documentation.

So what you get is correct - the year is 4411 BCE.

SQL> select to_char(trunc(sysdate), 'J') from dual;

TO_CHAR
-------
2455264


Current date is approx 20 times your julian date.
Re: Convertion of Julian Date into dd/mm/yyyy [message #446429 is a reply to message #446421] Mon, 08 March 2010 05:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If 110048 is number for date '19/04/2211' then it is easy to find the starting point (day 0):
SQL> select to_date('19/04/2211','DD/MM/YYYY')-110048 from dual;
TO_DATE('19/04/2211
-------------------
30/12/1909 00:00:00

Regards
Michel
Re: Convertion of Julian Date into dd/mm/yyyy [message #446431 is a reply to message #446429] Mon, 08 March 2010 05:49 Go to previous messageGo to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Sorry Michel,
But I didn't get you as what you are trying to say.
Re: Convertion of Julian Date into dd/mm/yyyy [message #446435 is a reply to message #446431] Mon, 08 March 2010 05:55 Go to previous message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I said:
1/ Excel does not use Julian date
2/ Excel use a number of day from a starting day
3/ This starting point depends on your Excel version
4/ The example you gave (if it is correct) gives this starting point.

Regards
Michel
Previous Topic: Query to get output in a particular format (merged 3)
Next Topic: Precompiled sql
Goto Forum:
  


Current Time: Fri Dec 09 11:51:50 CST 2016

Total time taken to generate the page: 0.06658 seconds