Joined: 22 Aug 2005 Posts: 411 Location: Colarado, US
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?
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'
Joined: 22 Aug 2005 Posts: 411 Location: Colarado, US
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.
