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

 

 

 

 


Still have questions? We can help. Submit a case to Technical Support.

Last Modified On: April 21, 2017