"David" <dw*****@sc.rr.com> wrote in message
news:fD*****************@twister.southeast.rr.com. ..
I'm new to DB2 and I need to write a query that will allow me to find
specific dates instead of me having a date range asked for, I want it to
be calculated.
I've done this in Access by coding visual basic scripts and making monthly
reporting automatic. No longer having to change 15 queries to match
specific date ranges for the previous month is lots more efficient.
The way I coded it in Visual Basic is to make the begin date to equal the
1st of last month by subtracting a month and making the day = 1. Then for
ending date making month equal to current and day = 1 but subtracting a
day.
Computing dates in DB2 is really not hard most of the time, but there are a
few gotchas.
Do a search on "labeled durations" in the SQL Reference for your version of
DB2 to see the basics of date calculations.
Most date computations are pretty logical and give the result you'd expect,
*except* those that involve months but that's not DB2's fault, it's the
fault of the uneven months in the Gregorian calendar.
Examples (using ISO format, i.e. YYYY-MM-DD):
'2004-01-01' + 1 year = '2005-01-01', i.e. same month and day in the next
year
'2004-01-01' + 1 day = '2004-01-02', i.e. same year and month and next day
in that month
'2004-01-01' + 1 month = '2004-02-01', i.e. same year and day in the next
month
'2004-01-31' + 1 year = '2004-01-31', i.e. same month and day in the next
year
'2004-01-31' + 1 day = '2004-02-01', i.e. first day in the next month of the
same year
So far, this is about what you'd expect. Now a gotcha:
'2004-01-31' + 1 month = ???
DB2 wants to simply add 1 month to '2004-01-31' and give you '2004-02-31'
but it knows that there is no such date as Feb 31 in any year. Therefore, it
determines that 2004 is a leap year and gives you the last possible date in
February for a leap year, i.e. '2004-02-29'. You might agree that this is
reasonable or you might not, depending on your view on how date routines
work. If this bothers you, you can always add a fixed number of days, e.g.
30 days, instead of a month and then the result will always be predictable.
Another example:
'2004-01-31' + 2 months = ???
DB2 will simply add 2 months to the first date; if the result could really
exist, that is the result you get. In other words, DB2 would calculate
'2004-03-31' and since March 31 is a perfectly possible date, that's the
result you would get.
Another gotcha:
'2004-01-31' + 1 month + 1 month = ???
While this might look the same to you and me, it is NOT the same to DB2!
This example features two additions, not one, so the additions are done
separately and using the standard order of operations. In other words
('2004-01-31' + 1 month) + 1 month = '2004-02-29'
'2004-02-29 + 1 month = ???
The result of the first addition is Feb 29 2004, because that is the last
possible day in February in a leap year. The result of the second addition
is Mar 29 (!!), 2004 because that is the same day in March as we had in
February.
Therefore:
'2004-01-31' + 2 months <> '2004-01-31' + 1 month + 1 month
Again, if that sort of result disturbs you, always add a fixed number of
days to one date to get the next date.
Rhino
Rhino
