Troubleshooting
Problem
This document explains how to convert a MDY field to a Date Data Type using Query for i (Query/400).
Resolving The Problem
The following Query for i example converts a six-position numeric field in MMDDYY order to a date data type and selects records less than or equal to the current date:
Query . . . . . . . . . . . . . . . . . QRYDATE1
Library . . . . . . . . . . . . . . . QRYLIB
Query text . . . . . . . . . . . . . . Converting MDY to date data type
Selected files
ID File Library Member Record Format
T01 DATES QRYLIB *FIRST DATER
Result fields
Name Expression Column Heading Len
CHARMDY digits(datmdy)
MDY substr(charmdy,1,2)||'/'||
substr(charmdy,3,2)||'/'||
substr(charmdy,5,2)
MMDDYY date(mdy)
TODAY current(date)
Select record tests
AND/OR Field Test Value
MMDDYY LE TODAY
Ordering of selected fields
Field Sort Ascending/ Break Field
Name Priority Descending Level Text
DATMDY
CHARMDY
MDY
MMDDYY
TODAY
Results of QRYDATE1:
DATMDY CHARMDY MDY MMDDYY TODAY
000001 10,193 010193 01/01/93 01/01/93 05/25/96
000002 22,893 022893 02/28/93 02/28/93 05/25/96
000003 41,593 041593 04/15/93 04/15/93 05/25/96
000004 71,093 071093 07/10/93 07/10/93 05/25/96
000005 102,493 102493 10/24/93 10/24/93 05/25/96
000006 123,193 123193 12/31/93 12/31/93 05/25/96
Query . . . . . . . . . . . . . . . . . QRYDATE1
Library . . . . . . . . . . . . . . . QRYLIB
Query text . . . . . . . . . . . . . . Converting MDY to date data type
Selected files
ID File Library Member Record Format
T01 DATES QRYLIB *FIRST DATER
Result fields
Name Expression Column Heading Len
CHARMDY digits(datmdy)
MDY substr(charmdy,1,2)||'/'||
substr(charmdy,3,2)||'/'||
substr(charmdy,5,2)
MMDDYY date(mdy)
TODAY current(date)
Select record tests
AND/OR Field Test Value
MMDDYY LE TODAY
Ordering of selected fields
Field Sort Ascending/ Break Field
Name Priority Descending Level Text
DATMDY
CHARMDY
MDY
MMDDYY
TODAY
Results of QRYDATE1:
DATMDY CHARMDY MDY MMDDYY TODAY
000001 10,193 010193 01/01/93 01/01/93 05/25/96
000002 22,893 022893 02/28/93 02/28/93 05/25/96
000003 41,593 041593 04/15/93 04/15/93 05/25/96
000004 71,093 071093 07/10/93 07/10/93 05/25/96
000005 102,493 102493 10/24/93 10/24/93 05/25/96
000006 123,193 123193 12/31/93 12/31/93 05/25/96
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
6513228
Was this topic helpful?
Document Information
Modified date:
30 November 2024
UID
nas8N1010680