IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

month difference between two dates


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
lanand_hps

New User


Joined: 05 Dec 2007
Posts: 82
Location: chennai

PostPosted: Wed May 07, 2008 1:02 pm
Reply with quote

Hi,
how do we find difference between two dates in months?

SELECT MONTH(DATE ('2008-01-01) - ('2008-06-01')) FROM DUMMY;
will give 5 as output.

But,
SELECT MONTH(DATE('2007-01-01) - ('2008-06-01) FROM DUMMY;
is not giving me 16 months..
The actual answer is 1 year and 5 months.. But i want it as 16 months..

Pl help..

Thanks,
Anand
Back to top
View user's profile Send private message
acevedo

Active User


Joined: 11 May 2005
Posts: 344
Location: Spain

PostPosted: Wed May 07, 2008 2:19 pm
Reply with quote

Some Scarlar definitions (in version 7):

Quote:
The MONTH function returns the month part of its argument.
The DAY function returns the day part of its argument.
The DAYS function returns an integer representation of a date.


If I'm not wrong, MONTH won't give you the months between 2 dates but the month of the resulting date.
Back to top
View user's profile Send private message
Prajesh_v_p

Active User


Joined: 24 May 2006
Posts: 133
Location: India

PostPosted: Wed May 07, 2008 2:34 pm
Reply with quote

Can you just test it out whether this will work?

SELECT (YEAR(DATE('2008-06-01')) - YEAR(DATE('2007-01-01'))*12
+ (MONTH(DATE('2008-06-01'))- MONTH(DATE('2007-01-01')))

Thanks,
Prajesh
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed May 07, 2008 2:48 pm
Reply with quote

Prajesh_v_p,
close, but you need 1 additional paraenthesis
Code:

SELECT (
         YEAR(DATE('2008-06-01')) - YEAR(DATE('2007-01-01'))
       )  *12
      + (
         MONTH(DATE('2008-06-01'))- MONTH(DATE('2007-01-01'))
        )
FROM SYSIBM.SYSDUMMY1


lanand_hps,

1 year and 5 months is 17 not 16.
Back to top
View user's profile Send private message
acevedo

Active User


Joined: 11 May 2005
Posts: 344
Location: Spain

PostPosted: Wed May 07, 2008 2:51 pm
Reply with quote

using the DAYS function the SELECT becomes easier to understand (IMHO):

Code:
SELECT                                                       
       (DAYS(DATE('2008-01-01'))- DAYS(DATE('2008-06-01')))/30
      ,(DAYS(DATE('2007-01-01'))- DAYS(DATE('2008-06-01')))/30
FROM SYSIBM.SYSDUMMY1;                                       


will give you -5 and -17 (as Dick said)
Back to top
View user's profile Send private message
nabarundas

New User


Joined: 21 Jun 2007
Posts: 28
Location: pune

PostPosted: Wed May 07, 2008 3:01 pm
Reply with quote

visit the following link. this might be useful for you

http://www.ibm.com/developerworks/db2/library/techarticle/0211yip/0211yip3.html
Back to top
View user's profile Send private message
acevedo

Active User


Joined: 11 May 2005
Posts: 344
Location: Spain

PostPosted: Wed May 07, 2008 3:31 pm
Reply with quote

yes a really good link BUT here still v7.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Populate last day of the Month in MMD... SYNCSORT 2
No new posts JCL sort card - get first day and las... JCL & VSAM 9
No new posts replace word 'MONTH' with current mon... SYNCSORT 11
No new posts Timestamp difference and its average ... DB2 11
No new posts Difference when accessing dataset in ... JCL & VSAM 7
Search our Forums:

Back to Top