Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref

Author Message
lanand_hps

New User

Joined: 05 Dec 2007
Posts: 82
Location: chennai

 Posted: Wed May 07, 2008 1:02 pm    Post subject: month difference between two dates 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

acevedo

Active User

Joined: 11 May 2005
Posts: 344
Location: Spain

Posted: Wed May 07, 2008 2:19 pm    Post subject:

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.
Prajesh_v_p

Active User

Joined: 24 May 2006
Posts: 133
Location: India

 Posted: Wed May 07, 2008 2:34 pm    Post subject: 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
dbzTHEdinosauer

Global Moderator

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

Posted: Wed May 07, 2008 2:48 pm    Post subject:

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.
acevedo

Active User

Joined: 11 May 2005
Posts: 344
Location: Spain

Posted: Wed May 07, 2008 2:51 pm    Post subject:

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)
nabarundas

New User

Joined: 21 Jun 2007
Posts: 28
Location: pune

 Posted: Wed May 07, 2008 3:01 pm    Post subject: link visit the following link. this might be useful for you http://www.ibm.com/developerworks/db2/library/techarticle/0211yip/0211yip3.html
acevedo

Active User

Joined: 11 May 2005
Posts: 344
Location: Spain

 Posted: Wed May 07, 2008 3:31 pm    Post subject: yes a really good link BUT here still v7.
 All times are GMT + 6 Hours
 Page 1 of 1

Search our Forum:

 Topic Author Forum Replies Posted Similar Topics ISMF Difference between volume count ... upendrasri IBM Tools 2 Tue Dec 05, 2017 12:40 pm Comparing dates in different formats migusd SYNCSORT 4 Sat Nov 18, 2017 3:02 am Finding difference between two PS fil... Vignesh Sid SYNCSORT 5 Mon Oct 30, 2017 1:13 pm Difference in SORT & ICETOOL Mohan Kothakota DFSORT/ICETOOL 5 Fri Sep 22, 2017 4:56 pm Fetch Previous month & year in MM... girishb2 DFSORT/ICETOOL 3 Thu Sep 21, 2017 9:54 pm

 © 2003-2017 IBM MAINFRAME Software Support Division
 Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us