View previous topic :: View next topic
|
Author |
Message |
sanil m
New User
Joined: 24 Aug 2007 Posts: 38 Location: chennai
|
|
|
|
Hi,
I want to get the difference in months between two dates,
i tried the query,
Code: |
SELECT MONTH(DATE('2008-03-01') - DATE('2007-02-01'))
FROM SYSIBM.SYSDUMMY1; |
i am getting the answer as 1,
but i want it as 13.
please help me with this. |
|
Back to top |
|
|
ksk
Active User
Joined: 08 Jun 2006 Posts: 355 Location: New York
|
|
|
|
you are extracting month and subtracting, so it is giving 1. |
|
Back to top |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007 Posts: 1512 Location: Virginia, USA
|
|
|
|
sanil m wrote: |
Hi,
I want to get the difference in months between two dates,
i tried the query,
Code: |
SELECT MONTH(DATE('2008-03-01') - DATE('2007-02-01'))
FROM SYSIBM.SYSDUMMY1; |
i am getting the answer as 1,
but i want it as 13.
please help me with this. |
Code: |
select MONTH(DATE('2008-03-01') - DATE('2007-02-01')) + (YEAR(DATE('2008-03-01') - DATE('2007-02-01')) * 12) FROM SYSIBM.SYSDUMMY1;
|
But you still have the left over days which could be as high as 31. |
|
Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007 Posts: 10872 Location: italy
|
|
|
|
Quote: |
But you still have the left over days which could be as high as 31. |
Hi Craq,
sometimes I am boring to death ,
but it seems almost impossible to stick into people heads
that date calculations are not something that can be solved by a forum answers
but it is something that must be defined at company levels standards
and supported by the IT infrastructure with a proper set of general use subroutines/functions |
|
Back to top |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007 Posts: 1512 Location: Virginia, USA
|
|
|
|
I was on a project that involved a lot of date calculations and when we asked about date differences we got more answer then we could handle, some said todays-date - todays-date = 0, some said 1 and some said it depends. Todays-date - yesterdays-date = 1 (even from the people who said 1 for todays-date - todays-date) and some said that if the month was different then it was 2. We finally had to by pass the managers and go to the agency head. |
|
Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006 Posts: 6250 Location: Mumbai, India
|
|
|
|
Craq Giegerich wrote: |
(even from the people who said 1 for todays-date - todays-date) |
..and I was thining that I'm the only 'dumb' left in this world... |
|
Back to top |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007 Posts: 1512 Location: Virginia, USA
|
|
|
|
Anuj D. wrote: |
..and I was thining that I'm the only 'dumb' left in this world... |
If it wasn't for the dumb ones the rest of us would be out of work. |
|
Back to top |
|
|
|