IBM Support

Converting Julian Dates to Calendar Dates

Troubleshooting


Problem

Is there any way to translate a Julian date to a regular calendar (Gregorian) date in DecisionStream?

Resolving The Problem

Use the following calculation:

Select date (days(concat(cast(integer(1900000+"SDIVD") /1000 as Char(4)),'-01-01'))+mod(integer(1900000+" SDIVD"),1000)-1)

where SDIVD is the Julian date field.

This calculation breaks the Julian expression 99123 into 2 parts. The first part of the calculation creates the century date and adds the first day of the year, thus the 01-01. So it would convert 99123 in the first half of the expression to 1999-01-01. The second half of the equation adds days to the year based using the Mod command (remainder function). Thus, the mod of 99123 divided by 1000 is 123. It then adds 123 days to the beginning of the year from the first part of the equation, to give the correct date. If the source dates are already in the format of 1999123, then the 1900000 does not need to be added to the SDIVD field.

[{"Product":{"code":"SSTQPQ","label":"IBM Cognos Series 7 PowerPlay"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"DecisionStream","Platform":[{"code":"PF033","label":"Windows"}],"Version":"DecisionStream 6.5;DecisionStream 7.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Historical Number

98181

Document Information

Modified date:
15 June 2018

UID

swg21336399