For example, how can I convert text "032" which represents an ordinal date to "02/01" for February 1st?Thanks,
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));
Just use a format string specifying the julian date:select '032' (date, format 'ddd');select '2009032' (date, format 'yyyyddd');Dieter
I am trying to convert Julian date 47712 into date format. I used the above formula but it only works up to 41199?
You didn't define any rule to which date 47712 should map?
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.
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.
Sorry for jumping in.
Been tehre, done that.
Wrote about the whole thing here:
(in spanish, as usual).
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.
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.
I found this formula that works with future dates:
SELECT TRIM ( TO_DATE ( CAST ( 2415020 + CAST ( xxxxxx AS INT ) AS CHAR(7) ) , 'J' ) )