Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

when we subtract two date then require current month number

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
santosh100678

New User


Joined: 21 Sep 2009
Posts: 55
Location: Kolkatta

PostPosted: Wed Mar 17, 2010 6:34 pm    Post subject: when we subtract two date then require current month number
Reply with quote

Hi ,

I have a query regarding subtraction of two month

SELECT MONTH(DATE(('2009-07-31'))- DATE(('2010-02-28')))
FROM SYSIBM.SYSDUMMY1;
if i put this its work for all 11 month but for feb its shows wrong
Back to top
View user's profile Send private message

guptae

Moderator


Joined: 14 Oct 2005
Posts: 1187
Location: Bangalore,India

PostPosted: Wed Mar 17, 2010 6:38 pm    Post subject:
Reply with quote

Hello Satish,

It should have return you -6. Btw what result you are expecting from the above query?
Back to top
View user's profile Send private message
santosh100678

New User


Joined: 21 Sep 2009
Posts: 55
Location: Kolkatta

PostPosted: Wed Mar 17, 2010 8:44 pm    Post subject:
Reply with quote

if you count these month then month must 7 month

08-31
09-30
10-31
11-30
12-31
01-31
02-32
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Mar 17, 2010 8:57 pm    Post subject:
Reply with quote

Hello,

Quote:
if you count these month then month must 7 month
You are counting "your way" which is not the same as DB2. . . You need to change your expectation.

If you use 2010-03-01 you will get -7. . .
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Thu Mar 18, 2010 1:20 pm    Post subject:
Reply with quote

The value returned is an approximation
because it does not account for leap years and assumes only 30 days per month.

How is month difference supposed to work (according to you)?

Is 28/feb and 01/mar = 1 month ?
Is 28/feb and 28/mar = 1 month ?
Is 28/feb and 29/mar = 1 month ?
Is 28/feb and 30/mar = 1 month ?
Is 28/feb and 31/mar = 1 month ?
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Reg:Displaying the particular month o... bhavana yalavarthi All Other Mainframe Topics 4 Sat Jul 08, 2017 8:13 pm
No new posts How to add header with Date(YYMMDD) i... Rajan Moorthy DFSORT/ICETOOL 2 Thu Jul 06, 2017 11:44 pm
No new posts Validate date and numeric fields and ... Rick Silvers DFSORT/ICETOOL 6 Thu May 11, 2017 6:51 pm
No new posts SMTP the current generation number of... Jyothi Kulunde JCL & VSAM 4 Thu May 04, 2017 4:08 pm
No new posts LISTIDR compiled date/time jerryte IBM Tools 3 Thu Apr 20, 2017 7:37 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us