Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

MONTHS_BETWEEN

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
LearningDb2

New User


Joined: 19 Jun 2009
Posts: 40
Location: India

PostPosted: Fri Jun 03, 2011 7:51 pm    Post subject: MONTHS_BETWEEN
Reply with quote

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
View user's profile Send private message

enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10211
Location: italy

PostPosted: Fri Jun 03, 2011 8:38 pm    Post subject: Reply to: MONTHS_BETWEEN
Reply with quote

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
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Fri Jun 03, 2011 9:50 pm    Post subject:
Reply with quote

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
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10211
Location: italy

PostPosted: Fri Jun 03, 2011 10:22 pm    Post subject: Reply to: MONTHS_BETWEEN
Reply with quote

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
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Mon Jun 06, 2011 11:47 am    Post subject:
Reply with quote

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
View user's profile Send private message
Jeevankumar.Pochammala

New User


Joined: 04 May 2011
Posts: 7
Location: INDIA

PostPosted: Sun Jun 12, 2011 9:09 pm    Post subject:
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:



Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us