|
View previous topic :: View next topic
|
| Author |
Message |
LearningDb2
New User
Joined: 19 Jun 2009
Posts: 40
Location: India
|
|
|
|
Hi,
I am working on DB2 on z/os v 9.1
I need to generate a report for the number of accounts opened in the duration of one month.
I tried a query
SELECT COUNT(*) FROM Table1 A
WHERE ((MONTHS_BETWEEN('<express1>',A.ISS_DATE))) BETWEEN 0 AND 1
The query works fine but when i give <expression1> as '2005-12-31', it retuns the rows with date 30-11-2005 also.
I mean if I am running the report at 31st December then i should get the records from 1st of December and not from 30th November.
Please ignore my ignorance and guide me in how to approach.
Note-: exp1 is always greater than ISS_DATE. |
|
| Back to top |
|
 |
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10873
Location: italy
|
|
|
|
read the manuals about the quirks of date computations, and the relative day adjustments !
2005-12-31 minus one month ==> 2005-11-30
the between clause includes the extremes ..
whence between returns data also for nov 30 |
|
| Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
Hey LearningDb2,
Try this,
| Code: |
select * from table1
where ts between
last_day(current date - 1 month)+1 day and last_day(current date) |
Thanks,
Sushanth |
|
| Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10873
Location: italy
|
|
|
|
| Quote: |
| I need to generate a report for the number of accounts opened in the duration of one month. |
as usual the description of the requirement and the sample provided just confuse things
the description as expressed implies a sliding window
so it is ok to go thru the hassle of subtracting one month from the current date and add one day
using as <current date> the last day of the month is just obfuscation
so Sushanth no need for the last_day stuff
just a BETWEEN ( CURRENT DATE - 1 MONTH ) + 1 DAY and (current date )
it will give one elapsed month
if on the other side the TS wants the <data> relative to the CURRENT MONTH>
no need for the between
just a WHERE clause for the month should be enough |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| Code: |
| where year(iss_date) = year(current date) and month(iss_date) = month(current date) |
willl work,but a better way would be to calculate first and last day of the month so no functions are needed on a column :
| Code: |
| where iss_date between current date - (days(current date) - 1) days and last_day(current date) |
|
|
| Back to top |
|
|
Jeevankumar.Pochammala
New User
Joined: 04 May 2011
Posts: 7
Location: INDIA
|
|
|
|
Hello Learingdb2,
you can try this query i hope it will work
Select count(*)
from Table1
where DATE(column name) between (current_date - 1 month) and current_date;
Thanks
Jeevan |
|
| Back to top |
|
|
|
|
