How to convert Ordinal (Julian) Date to Calendar Date

Tools
Highlighted
N/A

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,
10 REPLIES
Teradata Employee

Re: How to convert Ordinal (Julian) Date to Calendar Date

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));
Junior Apprentice

Re: How to convert Ordinal (Julian) Date to Calendar Date

Just use a format string specifying the julian date:

select '032' (date, format 'ddd');
select '2009032' (date, format 'yyyyddd');

Dieter

Enthusiast

Re: How to convert Ordinal (Julian) Date to Calendar Date

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?


Junior Apprentice

Re: How to convert Ordinal (Julian) Date to Calendar Date

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


Dieter


Enthusiast

Re: How to convert Ordinal (Julian) Date to Calendar Date

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. 


Junior Apprentice

Re: How to convert Ordinal (Julian) Date to Calendar Date

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


Junior Supporter

Re: How to convert Ordinal (Julian) Date to Calendar Date

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.


Enthusiast

Re: How to convert Ordinal (Julian) Date to Calendar Date

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


Enthusiast

Re: How to convert Ordinal (Julian) Date to Calendar Date

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