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
 

 

How DB2 handles Date Arithmetic

 
Post new topic   This topic is locked: you cannot edit posts or make replies.    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
iknow

Active User


Joined: 22 Aug 2005
Posts: 413
Location: Colarado, US

PostPosted: Wed Dec 07, 2005 8:22 am    Post subject: How DB2 handles Date Arithmetic
Reply with quote

Hi folks,

I'm confused about how DB2 handles date arithmetic! It seems that I can subtract a date from a date, or a time from a time, but what will DB2 return as the result of such a calculation?

Please help?
Back to top
View user's profile Send private message

khamarutheen

Active Member


Joined: 23 Aug 2005
Posts: 680
Location: NJ

PostPosted: Wed Dec 07, 2005 10:56 am    Post subject: Date
Reply with quote

Hi frnd,
The DB2 will return according to our specification. ie., if we need the difference in month then it gives it else in date then it gives in date...

the following are some functions used in date calculations

YEAR
YEARS
MONTH
MONTHS
DAY
DAYS
HOUR
HOURS
MINUTE
MINUTES
SECOND
SECONDS
MICROSECOND
MICROSECONDS

Code:
Eg: Date('5/31/1977') + 1 MONTH will give the o/p as '6/30/1977'



Hope useful
Back to top
View user's profile Send private message
EnjoyMF

New User


Joined: 27 May 2005
Posts: 88

PostPosted: Wed Dec 07, 2005 12:03 pm    Post subject:
Reply with quote

Hi friend

check the below site for more on date
http://www-128.ibm.com/developerworks/db2/library/techarticle/0211yip/0211yip3.html


OR u can find in the apendix of DB2 By C.J DATE

Cheers...prasad
Back to top
View user's profile Send private message
iknow

Active User


Joined: 22 Aug 2005
Posts: 413
Location: Colarado, US

PostPosted: Thu Dec 08, 2005 7:00 am    Post subject: Re: How DB2 handles Date Arithmetic
Reply with quote

Hi There,

Thanks for your valuable information.

I would also interested in adding some more points to this query.

DB2 enables you to add and subtract DATE, TIME, and TIMESTAMP columns. In addition, you can add date and time durations to or subtract them from these columns. But use date and time arithmetic with care. If you do not understand the capabilities and features of date and time arithmetic, you will likely encounter some problems implementing it.

Keep the following rules in mind:

When you issue date arithmetic statements using durations, do not try to establish a common conversion factor between durations of different types. For example, the following two date arithmetic statements are not equivalent:
Code:
 1997/04/03 - 1 MONTH
 1997/04/03 - 30 DAYS


April has 30 days, so the normal response would be to subtract 30 days to subtract one month. The result of the first statement is 1997/03/03, but the result of the second statement is 1997/03/04. In general, use like durations (for example, use months or use days, but not both) when you issue date arithmetic.

If one operand is a date, the other operand must be a date or a date duration. If one operand is a time, the other operand must be a time or a time duration. You cannot mix durations and data types with date and time arithmetic.

If one operand is a timestamp, the other operand can be a time, a date, a time duration, or a date duration. The second operand cannot be a timestamp. You can mix date and time durations with timestamp data types.

Now, what exactly is in that field returned as the result of a date or time calculation? Simply stated, it is a duration. There are three types of durations: date durations, time durations, and labeled durations.

Date durations are expressed as a DECIMAL(8,0) number. To be properly interpreted, the number must have the format yyyymmdd, where yyyy represents the number of years, mm the number of months, and DD the number of days. The result of subtracting one DATE value from another is a date duration.

Time durations are expressed as a DECIMAL(6,0) number. To be properly interpreted, the number must have the format hhmmss, where hh represents the number of hours, mm the number of minutes, and ss the number of seconds. The result of subtracting one TIME value from another is a time duration.

Labeled durations represent a specific unit of time as expressed by a number followed by one of the seven duration keywords: YEARS, MONTHS, DAYS, HOURS, MINUTES, SECONDS, or MICROSECONDS. A labeled duration can only be used as an operand of an arithmetic operator, and the other operand must have a data type of DATE, TIME, or TIMESTAMP.
For example:

Code:
CURRENT DATE + 3 YEARS + 6 MONTHS


This will add three and a half years to the current date.

Hope this helps.
Back to top
View user's profile Send private message
Jag

New User


Joined: 07 Dec 2005
Posts: 30
Location: India

PostPosted: Sun Dec 11, 2005 1:17 pm    Post subject: HI Friends
Reply with quote

Not only date or month we can even deduct or add the hours,seconds,micro seconds along with the month,year and date from timestamp

you can try this as an example

Select current timestamp-1 hour
from sysibm.sysdummy
Back to top
View user's profile Send private message
iknow

Active User


Joined: 22 Aug 2005
Posts: 413
Location: Colarado, US

PostPosted: Mon Dec 12, 2005 6:42 am    Post subject: Re: How DB2 handles Date Arithmetic
Reply with quote

Hi Jag,

Thanks for your information.
Back to top
View user's profile Send private message
Jag

New User


Joined: 07 Dec 2005
Posts: 30
Location: India

PostPosted: Mon Dec 12, 2005 7:04 pm    Post subject: Re: How DB2 handles Date Arithmetic
Reply with quote

No need to say say thanks :-)

Cheers,
Jag.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   This topic is locked: you cannot edit posts or make replies.    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 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 LISTIDR compiled date/time jerryte IBM Tools 3 Thu Apr 20, 2017 7:37 pm
No new posts SUBSTR for Date functions Shaheen Shaik DB2 4 Thu Apr 06, 2017 2:14 pm
No new posts Date and time format in CICS Chandru3183 CICS 2 Sat Mar 18, 2017 12:46 pm


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