convert julian 5 digit number to regular date

General
Enthusiast

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?

Tags (1)
1 REPLY
Junior Contributor

Re: convert julian 5 digit number to regular date

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')