All Forums General
Alsallam 9 posts Joined 04/15
31 Aug 2015
convert julian 5 digit number to regular date

I have the following field with julian numbers:

Date_Created
14304
14365
and so on.
I used the following formula
SELECT  CAST(CAST(Date_Created AS CHAR(7)) AS DATE FORMAT 'yyddd') FROM US_DWRP_TMO_R01.TDAIMFO
However, the returned date was in the wrong year (1900), for example:

10/31/1914, 02/27/1915
these dates should have been 2014 and 2015
any idea how to fix this?
 

dnoeth 4628 posts Joined 11/04
31 Aug 2015

This behaviour is controlled by a global setting (CenturyBreak in dbccontrol).
Simply add '20' before casting:

CAST('20'||trim(Date_Created) AS DATE FORMAT 'yyyyddd')

 

Dieter

You must sign in to leave a comment.