Problem Statement:
Can a date be converted from standard YYYYMMDD format to a Julian date in CYYDDD format?
Solution:
Use CASE logic with substring and concatenate as shown in the examples below. Both result in a 6 position numeric field in CYYDDD format. This can either be written to a table for future use or used within a date wrapper of DATE(date expression, CYYDDD). Example 3 shows how to convert a Julian CYYDDD date value to an eight position character DATE in YYYYMMDD format.
Example 1:
CASE WHEN (SUBSTRING(RIGHT(DIGITS(YEAR(DATE(DATECHAR1,
YYYYMMDD,CHAR))),4),1,2)) < '20' THEN ('0') ELSE ('1')
END || SUBSTRING(RIGHT(DIGITS(YEAR(DATE(DATECHAR1,YYYYMMDD,
CHAR))),4),3,2) || RIGHT(DIGITS(DAYOFYEAR(DATE(DATECHAR1,
YYYYMMDD,CHAR))),3) AS JULIANDATE
Example 2:
CAST(CASE (SUBSTRING(CAST(YEAR(DATE(DATECHAR1,YYYYMMDD,CHAR))
AS CHAR(4)),1,2))
WHEN ( '19' ) THEN ( '0' )
WHEN ( '20' ) THEN ( '1' )
WHEN ( '21' ) THEN ( '2' )
END || SUBSTRING(CAST(YEAR(DATE(DATECHAR1,YYYYMMDD,CHAR))
AS CHAR(4)),3,2) ||
CAST(DAYOFYEAR(DATE(DATECHAR1,YYYYMMDD,CHAR))AS CHAR(3))
AS DECIMAL(6)) AS JULIAN2
Example 3: Converting a Julian date (CYYDDD) to an eight position character date in YYYYMMDD format.
DATE(STRIP(CAST(YEAR(DATE(ABUPMJ,CYYDDD))AS CHAR(4))) ||
CASE
WHEN (LENGTH(STRIP(CAST(MONTH(DATE(ABUPMJ,CYYDDD))AS CHAR(2)))) = 1)
THEN ('0' || STRIP(CAST(MONTH(DATE(ABUPMJ,CYYDDD))AS CHAR(2))))
ELSE (STRIP(CAST(MONTH(DATE(ABUPMJ,CYYDDD))AS CHAR(2)))) END ||
CASE
WHEN (LENGTH(STRIP(CAST(DAY(DATE( ABUPMJ,CYYDDD))AS CHAR(2)))) = 1)
THEN ('0' || STRIP(CAST(DAY(DATE(ABUPMJ,CYYDDD))AS CHAR(2))))
ELSE (STRIP(CAST(DAY(DATE(ABUPMJ,CYYDDD))AS CHAR(2))))
END, YYYYMMDD, CHAR)
Ref#: 1478303
