re: Query/400 and dates

//--- forwarded letter -------------------------------------------------------
> X-Mailer: Mozilla 4.04 [en] (Win95; I)
> Date: Fri, 27 Feb 98 22:20:02 -0500
> From: "Booth Martin" <boothm@ibm.net>
> To: "midrange-l@midrange.com" <midrange-l@midrange.com>
> Reply-To: MIDRANGE-L@midrange.com
> Subject: re: Query/400 and dates

> 
> Query/400 can work with date fields.  But how is an easy way to do the
> following:
> QSYSVAL for the date format is  *MDY.  The date field BIRTHDT in the
> record is yymmdd.
> We want a report showing each person's age, in years.
> 
> 
> Any ideas?
> 

Hello Booth,

This request is a little tricky because you are after the AGE which cannot be 
extracted by 
simply finding the difference in years.  I initially thought the following 
would work:

Assuming your job date format is *MDY

AGE = YEAR(CURRENT(DATE) - DATE(SUBSTR(DIGITS(BIRTHDT),3,2) || '/' ||
                                SUBSTR(DIGITS(BIRTHDT),5,2) || '/' ||
                                SUBSTR(DIGITS(BIRTHDT),1,2) ) )

but that just extracts the difference between the YEAR components and doesn't 
appear to 
account for rolling past the birth date.  For example, a current date of 
1998/08/01 and a 
birth date of 1962/08/13 should result in an AGE of 35, this person would not 
be 36 until 
1998/08/13.

I think you can achieve this by:

AGE = DAYS(CURRENT(DATE) - DATE(SUBSTR(DIGITS(BIRTHDT),3,2) || '/' ||
                                SUBSTR(DIGITS(BIRTHDT),5,2) || '/' ||
                                SUBSTR(DIGITS(BIRTHDT),1,2) ) ) / 365.25

where AGE is a zero decimal field.

If you are concerned about performance you could do the DIGITS function once 
into a temporary 
field and use SUBSTR on that.

Note the separator must match the job date separator and the date format 
derived by the SUBSTR 
operations must match the job date format.

Note that you still have a 2-digit year problem regarding the BIRTHDT field so 
all date values 
in that field will be interpreted as being in the range 1940 to 2039.

Regards,
Simon Coulter.

//----------------------------------------------------------
// FlyByNight Software         AS/400 Technical Specialists
// Phone: +61 3 9419 0175      Mobile: +61 3 0411 091 400
// Fax:   +61 3 9419 0175      E-mail: shc@flybynight.com.au
// 
// Windoze should not be open at Warp speed.
 

+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---