By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,647 Members | 1,618 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,647 IT Pros & Developers. It's quick & easy.

How to get specified between dates

P: n/a
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

Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a

"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


Nov 12 '05 #2

P: n/a

"Rhino" <rh****@NOSPAM.sympatico.ca> wrote in message
news:e5******************@news20.bellglobal.com...

"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
OOPS! That should be '2005-01-31'.
'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 should 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'
Sorry, that is NOT right. I meant to leave the result as question marks,
then develop the answer in stages.
'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

Nov 12 '05 #3

P: n/a
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(Current_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(Current_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(Current_Date - 1 Month) + 1 Day - 1 Month
and
Last_Day(Current_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" <rh****@NOSPAM.sympatico.ca> wrote in message
news:Vs*******************@news20.bellglobal.com.. .

"Rhino" <rh****@NOSPAM.sympatico.ca> wrote in message
news:e5******************@news20.bellglobal.com...

"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


OOPS! That should be '2005-01-31'.
'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

should
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'

Sorry, that is NOT right. I meant to leave the result as question marks,
then develop the answer in stages.
'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


Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.