IBM Support

Converting a Julian Date to MMDDYY Format Using SQL

Troubleshooting


Problem

This document describes a method that can be used to convert a five-digit numeric field in the format of YYDDD into a date data type.

Resolving The Problem

Following is a method that can be used to convert a five-digit numeric field with Julian Date data in the form YYDDD into a field with a date data type:

SELECT   DATE('19'||DIGITS(yyddd)) from library/file

To have the results returned in a certain date format, add the CHAR function. If the format of the current job is desired, typically MMDDYY, use the following:

SELECT   CHAR(DATE('19'||DIGITS(yyddd))) from library/file

[{"Product":{"code":"SWG60","label":"IBM i"},"Business Unit":{"code":"BU009","label":"Systems - Cognitive"},"Component":"DB2 for IBM i","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"7.1.0;6.1.1;6.1.0;5.4.5;5.4.0","Edition":""},{"Product":{"code":"SSC52E","label":"IBM i 7.1"},"Business Unit":{"code":"BU009","label":"Systems - Cognitive"},"Component":" ","Platform":[{"code":"","label":null}],"Version":"","Edition":""},{"Product":{"code":"SSC3X7","label":"IBM i 6.1"},"Business Unit":{"code":"BU009","label":"Systems - Cognitive"},"Component":" ","Platform":[{"code":"","label":null}],"Version":"","Edition":""}]

Historical Number

29901454

Document Information

Modified date:
18 December 2019

UID

nas8N1016596