subtract dates in AS400 query
Last Post 12 May 2009 01:13 PM by MICHAEL MAYER. 5 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages Not Resolved
Jim Suiter
Veteran Member
Veteran Member
Posts:1567
Avatar

--
08 May 2009 02:40 PM
Is there any way to determine the number of days between 2 dates in AS400 query?
Jon Juracich
Advanced Member
Advanced Member
Posts:492
Avatar

--
08 May 2009 04:04 PM Accepted Answer
Jim: If you take a look at the "Query for iSeries" (or equivalent, depending on what version you're on) book, there are examples for doing just this thing. Note that it's a PITA, but you can do it... Jon J.
Lynne Noll
Senior Member
Senior Member
Posts:6567

--
08 May 2009 04:15 PM Accepted Answer
It is a piece of cake in SQL and QMQRY. The fields must be either date fields or convertable into date fields.

select days(date1)-days(date2)
Actually, if you are dealing with real date fields, it is pretty similar in Query/400, since the DAYS function works on the result screen. The hard part is converting non dates into dates.
Jon Juracich
Advanced Member
Advanced Member
Posts:492
Avatar

--
08 May 2009 04:52 PM Accepted Answer
Lynne:
It is a piece of cake in SQL and QMQRY. The fields must be either date fields or convertable into date fields.
Well, yeah. I was assuming (perhaps incorrectly) that Jim was working with "numeric" dates. Those are a PITA to work with. "Real" dates - no problemo. Jon J.
Jim Suiter
Veteran Member
Veteran Member
Posts:1567
Avatar

--
08 May 2009 05:06 PM Accepted Answer
Actually, if you are dealing with real date fields, it is pretty similar in Query/400, since the DAYS function works on the result screen. The hard part is converting non dates into dates.
Am working with real dates, and that is the answer I was looking for. Thanks.
MICHAEL MAYER
New Member
New Member
Posts:2

--
12 May 2009 01:13 PM Accepted Answer
Here's a way to not only determine the number of days between dates but also provide the actual day of the week Enjoy! Selected files ID File Library Member Record Format CT1 PPCTSSTL1 NJWICSYS *FIRST CTSSTREC MS1 PPMSSATL1 NJWICSYS *FIRST MSSATREC TD1 TODAYSDATE NJWICSYS *FIRST CURDT Join tests Type of join . . . . . . . . . . . . . Matched records with primary file Field Test Field CT1.SSTAGID EQ MS1.SATAGYID CT1.SSTADMSITE EQ MS1.SATADMSITE CT1.SSTSVCSITE EQ MS1.SATSVCSITE Result fields YYYY substr(digits(sstdttrans),1,4) Yr MM substr(digits(sstdttrans),5,2) Mn DD substr(digits(sstdttrans),7,2) Dy F_DATE date(mm||'/'||dd||'/'||yyyy) ANYSUNDAY date('01/01/1950') DIF_DAYS days(f_date) - days(anysunday) 9 0 NBR_WK (dif_days/7)-.51 8 0 F_WEEKDAY nbr_wk*7 DOW 'Monday Tuesday WednesdayThurs day Friday Saturday Sunday ' DAYNBR dif_days-f_weekday WEEK_DAY substr(dow,(dif_days-f_weekday-1) *9+1,9) X '/' Y '/' DSLT days(current(date))-days(f_date) DSLT Select record tests CT1.SSTDTTRANS GE TD1.THY32DAGO AND CT1.SSTDTTRANS LE TD1.TWO2DAGO AND CT1.SSTADMSITE EQ MS1.SATSVCSITE AND MS1.SATADMINID NE 398 AND MS1.SATSITEID NE 392 Ordering of selected fields Field Sort Ascending/ Break Field Name Priority Descending Level Text CT1.SSTAGID 10 A LOCAL AGENCY ID CT1.SSTADMSITE 20 A ADMINISTRATIVE SITE ID MS1.SATSVCSITE 30 A SERVICE SITE ID MS1.SATSVCNAME SERVICE SITE NAME CT1.SSTCHKOUT CHECKOUT WEEK_DAY MM X DD Y YYYY DSLT Summary functions: 1-Total, 2-Average, 3-Minimum, 4-Maximum, 5-Count Overrides CT1.SSTAGID 0 Agy 2 ID CT1.SSTADMSITE 1 Adm 2 ID MS1.SATSVCSITE 1 SS 2 ID MS1.SATSVCNAME 1 SS 50 40 Name CT1.SSTCHKOUT 1 Check 1 Out WEEK_DAY 2 LT 9 Day- Date MM 2 *NONE 2 X 0 *NONE 1 DD 0 *NONE 2 Y 0 *NONE 1 YYYY 0 *NONE 4 DSLT 2 Day Count 9 0 3 0 Yes Report looks like this ... Last Transmission Date of Admin Sites. Tuesday - Friday Report. Agy Adm SS SS Chk LTrans Day ID ID ID Name Out Day-Date Count 14 01 01 Plainfield WIC Office N Friday-05/08/2009 4
You are not authorized to post a reply.

Acceptable Use Policy