Troubleshooting
Problem
This document explains how Query for i (Query/400) can be used to calculate duration between current date and MDY date.
Resolving The Problem
Following is a Query for i example of calculating the duration in days between a 6-position numeric field in MMDDYY sequence and the current date:
Query . . . . . . . . . . . . . . . . . QRYDATE2
Library . . . . . . . . . . . . . . . QRYLIB
Query text . . . . . . . . . . . . . . Calculating duration in days
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)
FILEDAYS DAYS(MMDDYY)
TODAYDAYS DAYS(TODAY)
DIFFDAYS TODAYDAYS - FILEDAYS
Ordering of selected fields
Field Sort Ascending/ Break Field
Name Priority Descending Level Text
DATMDY
MMDDYY
TODAY
FILEDAYS
TODAYDAYS
Results of QRYDATE2:
DATMDY MMDDYY TODAY FILEDAYS TODAYDAYS DIFFDAYS
000001 10,193 01/01/93 05/25/96 727,564 728,804 1,240
000002 22,893 02/28/93 05/25/96 727,622 728,804 1,182
000003 41,593 04/15/93 05/25/96 727,668 728,804 1,136
000004 71,093 07/10/93 05/25/96 727,754 728,804 1,050
000005 102,493 10/24/93 05/25/96 727,860 728,804 944
000006 123,193 12/31/93 05/25/96 727,928 728,804 876
Query . . . . . . . . . . . . . . . . . QRYDATE2
Library . . . . . . . . . . . . . . . QRYLIB
Query text . . . . . . . . . . . . . . Calculating duration in days
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)
FILEDAYS DAYS(MMDDYY)
TODAYDAYS DAYS(TODAY)
DIFFDAYS TODAYDAYS - FILEDAYS
Ordering of selected fields
Field Sort Ascending/ Break Field
Name Priority Descending Level Text
DATMDY
MMDDYY
TODAY
FILEDAYS
TODAYDAYS
Results of QRYDATE2:
DATMDY MMDDYY TODAY FILEDAYS TODAYDAYS DIFFDAYS
000001 10,193 01/01/93 05/25/96 727,564 728,804 1,240
000002 22,893 02/28/93 05/25/96 727,622 728,804 1,182
000003 41,593 04/15/93 05/25/96 727,668 728,804 1,136
000004 71,093 07/10/93 05/25/96 727,754 728,804 1,050
000005 102,493 10/24/93 05/25/96 727,860 728,804 944
000006 123,193 12/31/93 05/25/96 727,928 728,804 876
[{"Product":{"code":"SWG60","label":"IBM i"},"Business Unit":{"code":"BU070","label":"IBM Infrastructure"},"Component":"DB2 for IBM i","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"Version Independent","Edition":"","Line of Business":{"code":"LOB68","label":"Power HW"}}]
Historical Number
6513553
Was this topic helpful?
Document Information
Modified date:
04 December 2024
UID
nas8N1010677