Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist 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: 6970
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 ISMF Difference between volume count ... upendrasri IBM Tools 2 Tue Dec 05, 2017 12:40 pm
No new posts Comparing dates in different formats migusd SYNCSORT 4 Sat Nov 18, 2017 3:02 am
No new posts Finding difference between two PS fil... Vignesh Sid SYNCSORT 5 Mon Oct 30, 2017 1:13 pm
No new posts Difference in SORT & ICETOOL Mohan Kothakota DFSORT/ICETOOL 5 Fri Sep 22, 2017 4:56 pm
No new posts Fetch Previous month & year in MM... girishb2 DFSORT/ICETOOL 3 Thu Sep 21, 2017 9:54 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us