RE: JBA date manipulation with DB2 Query manager

This is exactly how we work.  As part of our JBA overnight routine we create
a file with all format variations of the current date in, and refer to this
file in any queries that require a date. These values can then be
manipulated to add/subtract a number of days etc.

Stuart Allen, Fellowes


> -----Original Message-----
> From: david.kahn@gb.abb.com [SMTP:david.kahn@gb.abb.com]
> Sent: Wednesday, May 05, 1999 2:59 PM
> To:   MIDRANGE-L@midrange.com
> Subject:      Re: JBA date manipulation with DB2 Query manager
> 
> Dan Thomas <DThomas@lpw-mdi.com> wrote:
> 
> >JBA dates are stored as a seven digit number in the format
> >CYYMMDD, where century 1900 is a zero and 2000 is a one.
> >Any suggestions for being able to do date manipulation (aging,
> >days difference, etc) within DB2 query manager and/or PC
> >report writers like IQ Objects and Crystal Reports?
> 
> Under the heading "crude but effective" is an approach used successfully
> by
> a client of mine. Write a one-off program to generate a file of dates. The
> program steps day by day from an arbitrary start date to an arbitrary end
> date. Make sure that the range encompasses all the dates you will
> realistically use. Each record will contain all the date formats you are
> likely to use. So you might have CYYMMDD, Julian, absolute (day number
> relative to an arbitrary starting point), day of week number, day of week
> name, year number, week number, day number, MM/DD/YYYY, short display
> format, long display format, etc, etc, etc.
> 
> Your queries can now join to this file by the CYYMMDD (or any) field and
> retrieve all the other formats of the date. The absolute date can be used
> for arithmetic. Subtract one date from another to get the days between
> them, or add a number of days to any date then retrieve the result in any
> format.
> 
> Dave Kahn, ABB Steward Ltd.
> 
> 
> +---
> | This is the Midrange System Mailing List!
> | To submit a new message, send your mail to MIDRANGE-L@midrange.com.
> | To subscribe to this list send email to MIDRANGE-L-SUB@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
> +---
+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@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
+---