| Author |
Message |
lanand_hps
New User
Joined: 05 Dec 2007
Posts: 22
Location: chennai
|
|
|
|
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 |
|
 |
References
|
 Posted: Wed May 07, 2008 1:02 pm Post subject: Re: month difference between two dates |
|
|
|
|
acevedo
Active User
Joined: 11 May 2005
Posts: 250
Location: Spain
|
|
|
|
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 |
|
|
Prajesh_v_p
Warnings : 1
Active User
Joined: 24 May 2006
Posts: 97
Location: India
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Senior Member
Joined: 20 Oct 2006
Posts: 900
Location: germany
|
|
|
|
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 |
|
|
acevedo
Active User
Joined: 11 May 2005
Posts: 250
Location: Spain
|
|
|
|
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 |
|
|
nabarundas
New User
Joined: 21 Jun 2007
Posts: 9
Location: pune
|
|
| Back to top |
|
|
acevedo
Active User
Joined: 11 May 2005
Posts: 250
Location: Spain
|
|
|
|
| yes a really good link BUT here still v7. |
|
| Back to top |
|
|
|
|
