View previous topic :: View next topic
|
Author |
Message |
lanand_hps
New User
Joined: 05 Dec 2007 Posts: 82 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 |
|
|
acevedo
Active User
Joined: 11 May 2005 Posts: 344 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
Active User
Joined: 24 May 2006 Posts: 133 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
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
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: 344 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: 28 Location: pune
|
|
Back to top |
|
|
acevedo
Active User
Joined: 11 May 2005 Posts: 344 Location: Spain
|
|
|
|
yes a really good link BUT here still v7. |
|
Back to top |
|
|
|