Query/400 Julian Date Conversion

According to the Query/400 Use manual (SC41-5210), a seven-byte character string used with the DATE function is considered to be a Julian date in the format ccyyddd. To convert a Julian date in the format yyddd or cyyddd to a date data type date, you can use an expression such as the following:

DATE(SUBSTR(DIGITS(1900000 + JULDAT), 2, 7))

Field JULDAT must be defined as numeric for the expression to work.

To demonstrate what actually happens, let’s break down the expression into its two parts. First, the DIGITS operation:

DIGITS(1900000 + JULDAT)

DIGITS returns a character representation of a number. For a JULDAT field with the value 97225 or 097225, the result of this expression is 01997225.

Next, the DATE operation:

DATE(SUBSTR(DATE_A, 2, 7)

Using the SUBSTR function to “feed” the DATE function a character string exactly seven bytes long activates Query/400’s Julian date rule and results in the date 8/13/97 (the date format and separator character depend on your job’s DATFMT and DATSEP attributes).

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s