Troubleshooting
Problem
This document provides an example of how to convert a Julian date to a date data type using Query for i.
Resolving The Problem
Example of Converting a Julian Date in YYDDD Sequence to a Date Data Type in Query for i:
Query . . . . . . . . . . . . . . . . . QRYDATE3
Library . . . . . . . . . . . . . . . QRYLIB
Query text . . . . . . . . . . . . . . Converting julian date
Selected files
ID File Library Member Record Format
T01 DATES QRYLIB *FIRST DATER
Result fields
Name Expression Column Heading Len Dec
JULIAN datjul / 1000 2 0
JULYY julian * 1000 5 0
JULDAYS datjul-julyy
CHARJUL digits(datjul)
YY substr(charjul,1,2)
MDY '01/01/'||yy
MMDDYY date(mdy)
JULDATE mmddyy + juldays days - 1 days
Ordering of selected fields
Field Sort Ascending/ Break Field
Name Priority Descending Level Text
DATJUL
JULIAN
JULYY
JULDAYS
CHARJUL
YY
MDY
MMDDYY
JULDATE
DATSYS
Results of QRYDATE3:
DATJUL JULIAN JULYY JULDAYS CHARJUL YY MDY MMDDYY JULDATE
000001 93,059 93 93,000 59 93059 93 01/01/93 01/01/93 02/28/93
000002 93,105 93 93,000 105 93105 93 01/01/93 01/01/93 04/15/93
000003 93,191 93 93,000 191 93191 93 01/01/93 01/01/93 07/10/93
000004 93,297 93 93,000 297 93297 93 01/01/93 01/01/93 10/24/93
000005 93,365 93 93,000 365 93365 93 01/01/93 01/01/93 12/31/93
Note: Any additional explanation of this documentation might need to be pursued under a consulting agreement.
Query . . . . . . . . . . . . . . . . . QRYDATE3
Library . . . . . . . . . . . . . . . QRYLIB
Query text . . . . . . . . . . . . . . Converting julian date
Selected files
ID File Library Member Record Format
T01 DATES QRYLIB *FIRST DATER
Result fields
Name Expression Column Heading Len Dec
JULIAN datjul / 1000 2 0
JULYY julian * 1000 5 0
JULDAYS datjul-julyy
CHARJUL digits(datjul)
YY substr(charjul,1,2)
MDY '01/01/'||yy
MMDDYY date(mdy)
JULDATE mmddyy + juldays days - 1 days
Ordering of selected fields
Field Sort Ascending/ Break Field
Name Priority Descending Level Text
DATJUL
JULIAN
JULYY
JULDAYS
CHARJUL
YY
MDY
MMDDYY
JULDATE
DATSYS
Results of QRYDATE3:
DATJUL JULIAN JULYY JULDAYS CHARJUL YY MDY MMDDYY JULDATE
000001 93,059 93 93,000 59 93059 93 01/01/93 01/01/93 02/28/93
000002 93,105 93 93,000 105 93105 93 01/01/93 01/01/93 04/15/93
000003 93,191 93 93,000 191 93191 93 01/01/93 01/01/93 07/10/93
000004 93,297 93 93,000 297 93297 93 01/01/93 01/01/93 10/24/93
000005 93,365 93 93,000 365 93365 93 01/01/93 01/01/93 12/31/93
Note: Any additional explanation of this documentation might need to be pursued under a consulting agreement.
Related Information
[{"Type":"MASTER","Line of Business":{"code":"LOB68","label":"Power HW"},"Business Unit":{"code":"BU070","label":"IBM Infrastructure"},"Product":{"code":"SWG60","label":"IBM i"},"ARM Category":[{"code":"a8m0z0000000CQbAAM","label":"IBM i Db2-\u003EQuery\/400"}],"ARM Case Number":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"All Versions"}]
Historical Number
8205900
Was this topic helpful?
Document Information
More support for:
IBM i
Component:
IBM i Db2->Query/400
Software version:
All Versions
Operating system(s):
IBM i
Document number:
683159
Modified date:
30 November 2024
UID
nas8N1010174
Manage My Notification Subscriptions