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
 

 

month difference between two dates

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: month difference between two dates
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    Post subject:
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    Post subject:
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: 6967
Location: porcelain throne

PostPosted: Wed May 07, 2008 2:48 pm    Post subject:
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    Post subject:
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    Post subject: link
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    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts SORT to append Month and YEAR in MMYY... tisamf DFSORT/ICETOOL 1 Wed Mar 08, 2017 4:46 pm
No new posts Difference between TWALENG and TWASIZE Arunkumar Chandrasekaran CICS 3 Tue Jan 03, 2017 12:57 pm
This topic is locked: you cannot edit posts or make replies. How to pass the previous month date i... Suganya87 DFSORT/ICETOOL 5 Mon Oct 31, 2016 4:13 pm
No new posts How to get previous month in SYMNAMES Suganya87 SYNCSORT 9 Fri Oct 28, 2016 1:37 pm
No new posts Sticky: difference between ... DUMMY ... and ... enrico-sorichetti JCL & VSAM 0 Mon Oct 17, 2016 4:31 pm


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