DB2 provides a way to calculate date from a Julian day.
For example: 2008075 should match to the 75th day of 2008 and i.e. 2008-03-15.
If you try to do,
$ db2 values date(2008075)
You will get a date of 12/03/5498 but that is what you do not want. The argument to DATE was supplied as an INT. You must supply julian day as ‘2008075’ and it must be CHAR(7).
$ db2 values date(‘2008075′)
and you should get ’03/15/2008’.
What if, you want to calculate reverse date from a Julian day. For example, you should get ’03/15/2008′ from ‘2008075’.
Use the following SQL to get reverse from the Julian day.
$ db2 values cast(trim(char(year(‘2008-03-15’)))||REPEAT(‘0’,(3-LENGTH(TRIM(CHAR(DAYOFYEAR(‘2008-03-15’))))))||TRIM(CHAR(DAYOFYEAR(‘2008-03-15’))) as char(7))
and you will get ‘2008075’.
By the way, DB2 has a function called Julian_day but it gives the number of days from Jan 1, 4713 BC.
$ db2 values julian_day(‘2008-03-15’) and you will get number of days 2454541 since Jan 1, 4713 BC.
--#SET TERMINATOR @ DROP FUNCTION DATE_JULIAN @ CREATE FUNCTION DATE_JULIAN(IN_DATE DATE) RETURNS CHAR(7) RETURN VALUES CAST(TRIM(CHAR(YEAR(IN_DATE)))|| REPEAT('0',(3-LENGTH(TRIM(CHAR(DAYOFYEAR(IN_DATE))))))|| TRIM(CHAR(DAYOFYEAR(IN_DATE))) AS CHAR(7)) @ values date_julian(date('03/15/2008'))@ values date_julian(date('2008-03-15'))@
Above 2 values will return 2008075.