How To Convert a YYYDDD Julian Date from DB2 to Cognos 8

Technote (troubleshooting)


Problem(Abstract)

User only has date in YYYDDD Julian format in his DB2 database. This format cannot be used with Cognos.

Symptom

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.

Cause

Cognos does not recognize YYYDDD as a valid date format.

Environment

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.


Cross Reference information
Segment Product Component Platform Version Edition
Business Analytics Cognos Business Intelligence Framework Manager AIX, HP-UX, HP Itanium, Linux, Solaris, Windows 8.4

Rate this page:

(0 users)Average rating

Document information


More support for:

Cognos 8 Business Intelligence
Framework Manager

Software version:

8.4

Operating system(s):

AIX, HP Itanium, HP-UX, Linux, Solaris, Windows

Reference #:

1429153

Modified date:

2010-06-22

Translate my page

Machine Translation

Content navigation