IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Get the difference in months between two dates


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
sanil m

New User


Joined: 24 Aug 2007
Posts: 38
Location: chennai

PostPosted: Fri Feb 29, 2008 2:19 pm
Reply with quote

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
View user's profile Send private message
ksk

Active User


Joined: 08 Jun 2006
Posts: 355
Location: New York

PostPosted: Fri Feb 29, 2008 4:57 pm
Reply with quote

you are extracting month and subtracting, so it is giving 1.
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Fri Feb 29, 2008 6:29 pm
Reply with quote

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
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Fri Feb 29, 2008 6:33 pm
Reply with quote

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
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Fri Feb 29, 2008 7:14 pm
Reply with quote

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
View user's profile Send private message
Anuj Dhawan

Superior Member


Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India

PostPosted: Fri Feb 29, 2008 11:20 pm
Reply with quote

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... icon_smile.gif
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Fri Feb 29, 2008 11:22 pm
Reply with quote

Anuj D. wrote:
..and I was thining that I'm the only 'dumb' left in this world... icon_smile.gif


If it wasn't for the dumb ones the rest of us would be out of work.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Timestamp difference and its average ... DB2 11
No new posts Difference when accessing dataset in ... JCL & VSAM 7
No new posts Amount of days between two dates PL/I & Assembler 8
No new posts What is the difference between Taskty... Compuware & Other Tools 2
No new posts Difference between VALIDPROC and CHEC... DB2 3
Search our Forums:

Back to Top