How to get specified between dates

[David]

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.

I'd appreciate any queries or examples that could help me do this in DB2.
Thanks in advance,

David

[Rhino]

Re: How to get specified between dates


"David" <dwright@sc.rr. com> wrote in message
news:fD71d.256$ zA3.98364@twist er.southeast.rr .com...[color=blue]
> 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[/color]
be[color=blue]
> 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[/color]
day.[color=blue]
>[/color]
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

[Rhino]

Re: How to get specified between dates


"Rhino" <rhino1@NOSPAM. sympatico.ca> wrote in message
news:e591d.682$ lb5.204009@news 20.bellglobal.c om...[color=blue]
>
> "David" <dwright@sc.rr. com> wrote in message
> news:fD71d.256$ zA3.98364@twist er.southeast.rr .com...[color=green]
> > 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[/color]
> be[color=green]
> > calculated.
> > I've done this in Access by coding visual basic scripts and making[/color][/color]
monthly[color=blue][color=green]
> > 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[/color][/color]
the[color=blue][color=green]
> > 1st of last month by subtracting a month and making the day = 1. Then[/color][/color]
for[color=blue][color=green]
> > ending date making month equal to current and day = 1 but subtracting a[/color]
> day.[color=green]
> >[/color]
> Computing dates in DB2 is really not hard most of the time, but there are[/color]
a[color=blue]
> few gotchas.
>
> Do a search on "labeled durations" in the SQL Reference for your version[/color]
of[color=blue]
> DB2 to see the basics of date calculations.
>
> Most date computations are pretty logical and give the result you'd[/color]
expect,[color=blue]
> *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[/color]

OOPS! That should be '2005-01-31'.
[color=blue]
> '2004-01-31' + 1 day = '2004-02-01', i.e. first day in the next month of[/color]
the[color=blue]
> 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,[/color]
it[color=blue]
> determines that 2004 is a leap year and gives you the last possible date[/color]
in[color=blue]
> 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[/color]
should[color=blue]
> 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[/color]
predictable.[color=blue]
>
> 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'
>[/color]
Sorry, that is NOT right. I meant to leave the result as question marks,
then develop the answer in stages.
[color=blue]
> '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
>[/color]

[David]

Re: How to get specified between dates

Thanks for the help, I finally came up with what I needed.

To get the last day of the last month, I used something like this:
Last_Day(Curren t_Date - 1 Month)
same as: 2004-08-31
To get the first day of the last month was a bit harder... I had to:

Last_Day(Curren t_Date - 1 Month) + 1 Day - 1 Month
same as: 2004-08-01
This is getting the last day of Last Month adding a day to give you the
first of this month
and then subtracting a month to give you the first of last month.

Once I came up with the formulas for these "Auto Dates" implimenting them
into my queries was easy:

Where Myfield Between Last_Day(Curren t_Date - 1 Month) + 1 Day - 1 Month
and
Last_Day(Curren t_Date - 1 Month)

Now every month I will no longer need to worry how many days are in it, I
just run the reportswith a PROC without any editing or worries! :)



"Rhino" <rhino1@NOSPAM. sympatico.ca> wrote in message
news:Vsg1d.2116 $lb5.254744@new s20.bellglobal. com...[color=blue]
>
> "Rhino" <rhino1@NOSPAM. sympatico.ca> wrote in message
> news:e591d.682$ lb5.204009@news 20.bellglobal.c om...[color=green]
>>
>> "David" <dwright@sc.rr. com> wrote in message
>> news:fD71d.256$ zA3.98364@twist er.southeast.rr .com...[color=darkred]
>> > 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[/color]
>> be[color=darkred]
>> > calculated.
>> > I've done this in Access by coding visual basic scripts and making[/color][/color]
> monthly[color=green][color=darkred]
>> > 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[/color][/color]
> the[color=green][color=darkred]
>> > 1st of last month by subtracting a month and making the day = 1. Then[/color][/color]
> for[color=green][color=darkred]
>> > ending date making month equal to current and day = 1 but subtracting a[/color]
>> day.[color=darkred]
>> >[/color]
>> Computing dates in DB2 is really not hard most of the time, but there are[/color]
> a[color=green]
>> few gotchas.
>>
>> Do a search on "labeled durations" in the SQL Reference for your version[/color]
> of[color=green]
>> DB2 to see the basics of date calculations.
>>
>> Most date computations are pretty logical and give the result you'd[/color]
> expect,[color=green]
>> *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[/color]
>
> OOPS! That should be '2005-01-31'.
>[color=green]
>> '2004-01-31' + 1 day = '2004-02-01', i.e. first day in the next month of[/color]
> the[color=green]
>> 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,[/color]
> it[color=green]
>> determines that 2004 is a leap year and gives you the last possible date[/color]
> in[color=green]
>> 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[/color]
> should[color=green]
>> 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[/color]
> predictable.[color=green]
>>
>> 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'
>>[/color]
> Sorry, that is NOT right. I meant to leave the result as question marks,
> then develop the answer in stages.
>[color=green]
>> '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
>>[/color]
>
>[/color]