Skip to Main Content
  • Questions
  • How to convert julian date into regular date

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Vipin.

Asked: April 02, 2003 - 4:06 pm UTC

Last updated: June 24, 2024 - 5:04 am UTC

Version: 8.0.6

Viewed 50K+ times! This question is

You Asked

Hi Tom ,
We have to load some data in oracle from a flat file . The date in flat file is displayed in Julian date format so 31st Mar 2003 comes as 2003090(number of days since Jan 1st,2003) and 20th Mar 2003 comes as 2003069(again number of days since Jan 1st,2003) .
I have to convert Julian date(2003090) into 20030331(yyyymmdd) format.
Now oracle consider julian date as the number of days since Jan 1st,4712 bc and not in the way i got my data. So is their any function which i can use to convert a date given in 2003090 format into 20030331 format or do i have to write a user function for this ?
Your help will be greatly appreciated as always.
Thanks

and Tom said...

Oracle julian dates are actual julian dates -- yours is a century, year and day into the year.

You will not load into a yyyymmdd format (that is an output or input date mask).. Internally ALL dates are 7 bytes and contain the century, year, month, day, hour, minute, second. You can then format this date as you choose on output using TO_CHAR.

I assume you are using sqlldr, so, your control file will look like:

...
date_field
"trunc(to_date(substr(:date_field,1,4),'yyyy'),'yy') + substr(:date_field,5)-1",
...

will do it -- just take the year, create an Oracle date from it, trunc that to the first day of the year and add the number of days into the year to that.

Rating

  (16 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Specifying Date format

Abdul Samed, April 03, 2003 - 5:22 am UTC

Hi Tom,

I think Vipin gets data in the format YYYYDDD i.e the year followed by number of days since that year.

So he can straightaway specify the date format YYYYDDD. It'll convert that data into a valid date.

HTH.

Regards,
Aby

Tom Kyte
April 03, 2003 - 8:04 am UTC

DOH, sometimes the obvious escapes us... of course


...
field date "YYYYDDDD",
....

does it.

DOH??

Swapnil, April 03, 2003 - 8:45 am UTC

What is DOH??

Tom Kyte
April 03, 2003 - 9:04 am UTC

It is the sound Homer Simpson makes when he does something stupid...

</code> http://www.rpi.edu/~markhn/sounds.simpsons.html <code>

32dohs.wav

Its working now........

Vipin, April 03, 2003 - 2:58 pm UTC

I put the following in my control file and it worked fine:
fieldname POSITION(27:33) char "to_char(to_date(:fieldname,'yyyyddd'),'yyyymmdd')"

Thanks......

Tom Kyte
April 03, 2003 - 3:06 pm UTC

you are not putting a date in a number or a string are you????

dates go in dates.
numbers go in numbers.
strings go in strings.

never never never put a date in a number or a string -- someone will eventually put garbage in there and someone else will always try to to_date it so they can use the format they like for display -- or they'll want to add a day or a month or something and the garbage data will defeat them.

use a DATE.

column data type............

Vipin, April 03, 2003 - 4:05 pm UTC

I also prefer the same but i have been given an existing target table which i am not allowed to change so ......
I have one related query though.......the flat file has got a header record( which says STARTnnnnn where n is a numeric digit) and a footer record which says(ENDnnnnnnn). I have tried using a When condition to discard these 2 records but i could discard only one. Does when support OR operator ?
if i use when (1:5) != 'START' , it works but if i use
when ((1:5) != 'START' OR (1:3) != 'END') , it failed......
What am i missing ?
Thanks


Tom Kyte
April 03, 2003 - 9:07 pm UTC

you can use skip=1 to skip the first and the 1:3 != END to get rid of the footer

Working fine now.........

Vipin, April 03, 2003 - 10:56 pm UTC

Thanks for your help , Tom

convert julian to date

Sharon, February 13, 2008 - 1:02 am UTC

Hi Tom,
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 instead of returning that date it gives me 13/FEB/4604 BC 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 BC
I had set:
alter session set nls_date_format = 'DD-MM-YYYY AD';
PLEASE HELP ME GUYS ON HOW TO CONVERT THIS TO MATCH THE 12/02/2008

Thanks

Tom Kyte
February 13, 2008 - 1:01 pm UTC

I have this julian number 39490.4281134259

that is not a julian date. I have no idea what it is.

PLEASE HELP US TO HELP YOU BY DESCRIBING IN SPECIFICATION FORM what this unique sort of number you have here, that is not a julian date, represents.


Michel Cadot, February 13, 2008 - 1:43 pm UTC

RE: this julian number 39490.4281134259

Duke Ganote, February 14, 2008 - 2:24 pm UTC

The number seems ubiquitous. Here it is at ittoolbox:
http://tinyurl.com/ypjm2m
Tom Kyte
February 15, 2008 - 7:48 am UTC

not sure what ubiquity has to do with the fact that this is not a julian date?

http://en.wikipedia.org/wiki/Julian_day

All I wanted was "tell us what this really is, what is really means"

then, we can convert it.

source MS Excel?

John Gasch, February 15, 2008 - 3:05 pm UTC

Looks suspiciously like the source was an MS Excel spreadsheet.

Enter this number into an Excel cell, then change the cell format to a date format. This yields
"2/12/2008 10:16:29 AM"

Laughing out Loud

Jay, February 15, 2008 - 3:52 pm UTC

Damnnn... Laughing out Loud! Good catch Josh..

How did you think of inserting that weird number into excel??

hahaha...

Jay

oopss...

Jay, February 15, 2008 - 3:53 pm UTC

I mean.. John.. not Josh!!

Ubiquitous; and Excel

Duke Ganote, February 22, 2008 - 4:24 pm UTC

The number is ubiquitous because that question with that number was posted on several forums.

Interesting observation that the number came from Excel (win version, not mac). Excel help says day serial #1 is January 1, 1900, so #39448 is January 1, 2008 (39,448 days after January 1, 1900).

Thanks Josh

Sharon, May 18, 2008 - 2:06 pm UTC

Hi,
Josh thanks a lot. Sorry for thanking late, as I read ur answers just now. I had come to the same conclusion after researching with the source of the database and solved the problem.
Thanks once again.
Sharon.

ORA-01854: julian date must be between 1 and 5373484

akashlavande, March 25, 2023 - 10:06 am UTC

to_char(TO_DATE((2415020 + ACTION_DATE), 'J'),'YYYY-MM-DD') as ACTION_DATE,
*
ERROR at line 6:
ORA-01854: julian date must be between 1 and 5373484


Chris Saxon
March 27, 2023 - 1:07 pm UTC

What is ACTION_DATE?

A reader, June 19, 2024 - 2:44 pm UTC

How to convert julian date '22010' to normal date 10-jan-2022
Chris Saxon
June 21, 2024 - 9:59 am UTC

TO_DATE ( val, 'J' )

But 22010 is not a Julian date in 2022. It's in the 47th century BC!

Terminological confusion

mathguy, June 22, 2024 - 10:53 am UTC

... going back to the first answer (2003).

The date format element J is not called "Julian DATE" - it is called "Julian DAY", including in Oracle documentation of date format elements.

"Julian DATE" is an old term for "Ordinal date". The Wikipedia article on "Ordinal date", in the Nomenclature section, explains this. Some old db and spreadsheet software used the term "Julian date" for the DDD or YYYY-DDD format; this was changed to Ordinal date so as not to be confused with Julian DAY (or Julian Day Number, JDN, etc.)

In the original question, as well as the most recent one from 2024, the posters clearly use the term "Julian date" to refer to the "ordinal date" concept, where DDD is the "day of the year" (Oracle documentation). "Day of the year" is another name for "ordinal date", formerly known as "Julian date".

If this had been clarified from the outset, perhaps we wouldn't see so many follow-up questions, and answers referencing J instead of DDD. For the most recent question, the answer is probably TO_DATE(val, 'YYDDD') or perhaps TO_DATE(val, 'RRDDD').
Connor McDonald
June 24, 2024 - 5:04 am UTC

If this had been clarified from the outset

welcome to the world of asktom :-)

framing questions is a skill...and it seems to be a skill that is on the decline