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.
Historical Number
98181
Was this topic helpful?
Document Information
Modified date:
15 June 2018
UID
swg21336399