All Forums Tools
phanj 1 post Joined 01/09
06 Jan 2009
How to convert Ordinal (Julian) Date to Calendar Date

For example, how can I convert text "032" which represents an ordinal date to "02/01" for February 1st?Thanks,

Fred 579 posts Joined 08/04
06 Jan 2009

One possibility is to add the ordinal number of days to a base date, then format the results appropriately:select cast(cast(date'2000-12-31' + 032 as format 'mm/dd') as char(5));

dnoeth 3068 posts Joined 11/04
07 Jan 2009

Just use a format string specifying the julian date:select '032' (date, format 'ddd');select '2009032' (date, format 'yyyyddd');Dieter

Dieter

Ekladios 11 posts Joined 02/12
14 Dec 2012

I am trying to convert Julian date 47712 into date format. I used the above formula but it only works up to 41199?
Any idea?

dnoeth 3068 posts Joined 11/04
14 Dec 2012

You didn't define any rule to which date 47712 should map?
Dieter

Dieter

Ekladios 11 posts Joined 02/12
14 Dec 2012

This number comes from Oracle and we need to convert into TD data.
Can you give an example of how to convert this number and define the rules? I had many different methods and formulas that work with lower number. 

dnoeth 3068 posts Joined 11/04
14 Dec 2012

It's not an Oracle Julian date which is based on 4712 BC.
Why do you expect me to "convert this number and define the rules", this is your task my crystal ball is broken.
Dieter

Dieter

CarlosAL 338 posts Joined 04/08
17 Dec 2012

Sorry for jumping in.
Been tehre, done that.
Wrote about the whole thing here:
http://carlosal.wordpress.com/2009/07/14/importando-fechas-julianas-a-teradata/
(in spanish, as usual).
HTH.
Cheers.
Carlos.

Ekladios 11 posts Joined 02/12
17 Dec 2012

Many thanks Dieter, I never asked for your crystal ball or expect you to waist your time, I just posted a question and you replied to it.
Thanks,
Adel

Ekladios 11 posts Joined 02/12
17 Dec 2012

The julian date, 44712, received in the data file was an error. It was corrected later on by the vendor.
 
The formula I used, was posted earlier, is
SELECT CAST ( '1899-12-31' AS DATE ) + CAST ( xxxxx AS INTEGER )
Thank you all for your contributions.
 Adel
 

Ekladios 11 posts Joined 02/12
17 Dec 2012

I found this formula that works with future dates:
 
SELECT TRIM ( TO_DATE ( CAST ( 2415020 + CAST ( xxxxxx AS INT ) AS CHAR(7) ) , 'J' ) ) 
 
Adel

You must sign in to leave a comment.