Skip to Main Content
  • Questions

  • How to convert julian date into regular date

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Vipin.

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

Last updated: March 27, 2023 - 1:07 pm UTC

Version: 8.0.6

Viewed 10K+ 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

  (14 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


See http://www.orafaq.com/forum/m/299489/102589/#msg_299489

Regards
Michel

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?
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.