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.