How To Convert a YYYDDD Julian Date from DB2 to Cognos 8
User only has date in YYYDDD Julian format in his DB2 database. This format cannot be used with Cognos.
For example, 999001 (Julian Format) is 1999-01-01(Cognos Format), where YYY is the number of years following 1900, and the DDD is the day of the year (between 001-365). The date must somehow be converted in order for the user to use the date within Cognos. There is no DB2 function that automatically converts the value.
Cognos does not recognize YYYDDD as a valid date format.
Framework Manager, Report Studio
Resolving the problem
Use the following equation:
_add_days ( _add_years ( 1900-01-01, floor(integer ([Julian Date] )/1000)),cast((mod ( integer ([Julian Date] ),1000)-1), integer))
Essentially, the above equation divides the YYYDDD value by 1000, which retrieves YYY as the divisor via the floor() function. This divisor is added onto the starting date of 1900-01-01. This will give us the correct year of the date.
Then, the remainder of that division is retrieved via the mod() function, giving us DDD. This value is then added onto the date from the last step.
Note that the functions used in this example are DB2 functions.
|Business Analytics||Cognos Business Intelligence||Framework Manager||AIX, HP-UX, HP Itanium, Linux, Solaris, Windows||8.4|
More support for:
Cognos 8 Business Intelligence
Software version: 8.4
Operating system(s): AIX, HP-UX, Linux, Solaris, Windows
Reference #: 1429153
Modified date: 22 June 2010