Expressions › Arithmetic Operations for Dates, Times, and Timestamps › Durations

Durations

A duration is a number that represents an interval of time. The number may be any of the following:

• Function
• Expression
• Literal
• Column name
• Host variable
• Special registers
• Labeled durations

Special registers and labeled durations are CA Datacom/DB extensions. See Special Registers for information on special registers. See Labeled Duration for information on labeled durations.

Date Durations

A date duration represents a number of years, months, and days. A date duration is expressed as a DECIMAL(8,0). For example, a date duration of 1999 years, 3 months, and 20 days is 19990320.

Subtracting dates results in a date duration.

Time Durations

A time duration represents a number of hours, minutes and seconds. A time duration is expressed as DECIMAL(6,0). For example, a time duration of 19 hours, 30 minutes and 20 seconds is 193020.

Subtracting times results in a time duration.

Addition Rules for Dates, Times, and Timestamps

If operand one is a date, operand two must be a date duration or a labeled duration of years, months, or days.

If operand one is a time, operand two must be a time duration or a labeled duration of hours, minutes, or seconds.

If operand one is a timestamp, operand two must be a labeled duration, a date duration, or a time duration.

Subtraction Rules for Dates, Times, and Timestamps

The operation of subtracting two date-time values is different from the operation of subtracting a duration from a date-time value. The operands of date-time subtraction must be as follows:

• If operand one is a date, operand two must be a date, date duration, a date string, or a labeled duration of years, months, or days.
• If operand one is a time, operand two must be a time, time duration, a time string, or a labeled duration of hours, minutes, or seconds.
• If operand one is a timestamp, operand two must be a labeled duration, a time duration, or a date duration of years, months.
• If operand two is a date, operand one must be a date or date string.
• If operand two is a time, operand one must be a time or time string.
• A string representation of a date or time value cannot be subtracted from another string representation of a date or time value. However, if one of the strings is interpreted as a date or time, the expression is valid. For example:

  DATE('1989-03-10') - '1985-03-10' is valid, but ('1989-03-10' - '1985-03-10') is invalid.

Using Durations to Increment or Decrement Dates

The result of adding or subtracting a duration to or from a date is a date. If D is a date and N is a number defined as DECIMAL(15,0), the result of D + N YEARS or D - N YEARS is the date that is N years before or after D. Only years are counted. The month of the result is always the same as the month of D. The day of the result is the same as the day of D, unless the result is February 29th of a non-leap year, in which case the day part of the result is 28 and SQLWARN6 is set to W.

The result of D + N MONTHS or D - N MONTHS is the date that is N months before or after D. Only months and years (if necessary) are counted. The day of the result is the same as the day of D, unless the result would be an invalid date, in which case the day part of the result is the last day of the month and SQLWARN6 is set to W.

The result of D + N DAYS or D - N DAYS is the date that is N days before or after D.

If N is a duration of y years, m months, and d days, the result of D + N (where N is positive) or D - N (where N is negative) is the date that is y years, m months, and d days after D. The arithmetic is performed in this order using the previously defined rules, including the setting of SQLWARN6 whenever an end-of-month adjustment is made.

If N is a duration of y years, m months, and d days, the result of D - N (where N is positive) or D + N (where N is negative) is the date that is d days, m months, and y years before D. The arithmetic is performed in that order using the previously defined rules, including the setting of SQLWARN6 whenever an end-of-month adjustment is made.

Subtracting Dates

If D1 and D2 are dates, the result of D1 - D2 is a date duration that gives the number of years, months, and days between the two dates. The data type of the result is DECIMAL(8,0). If D1 is greater than or equal to D2, D2 is subtracted from D1. If D1 is less than D2, D1 is subtracted from D2 and the sign of the result is negative. Given the example D1 - D2, the rules for date subtraction are:

• If DAY(D2) is less than or equal to DAY(D1), the day part of the result is equal to DAY(D1) - DAY(D2).
• If DAY(D2) is greater than DAY(D1), the day part of the result is equal to N + DAY(D1) - DAY(D2), where N is the last day of MONTH(D2). MONTH(D2) is incremented by 1.
• If MONTH(D2) is less than or equal to MONTH(D1), the month part of the result is equal to MONTH(D1) - MONTH(D2).
• If MONTH(D2) is greater than MONTH(D1), the month part of the result is equal to 12 + MONTH(D1) - MONTH(D2). YEAR(D2) is incremented by 1.
• The year part of the result is equal to YEAR(D1) - YEAR(D2).

Special Considerations Relating to Date Arithmetic

Because of the differing number of days in each month of the year, adding a month to a given date does not always result in the same day of the next month. For example, adding one month to January 31 would yield February 31, which is not a valid date. The result is adjusted back to the last day of the month, February 28. Therefore, D + N MONTHS - N MONTHS is not always equal to D.

To avoid inconsistencies in date arithmetic caused by months, use days rather than months. For example, DATE(DAYS(D1) + DAYS(D2) - DAYS(D3)) gives accurate results, but D1 + (D2 - D3) may not give accurate results.

Incrementing and Decrementing Times by Durations

The result of adding or subtracting a duration to or from a time is a time. The result is in the range of times. If T is a time and N is a number defined as DECIMAL(15,0):

• The result of T + N HOURS or T - N HOURS is a time that is N hours before or after T. Only hours are counted. The minute and second of the result are the same as the minute and second of T.
• The result of T + N MINUTES or T - N MINUTES is the time that is N minutes before or after T. Only minutes and hours (if necessary) are counted. The second of the result is the same as T.
• The result of T + N SECONDS or T - N SECONDS is the time that is N seconds before or after T.
• If N is a duration of h hours, m minutes, and s seconds, the result of T + N or T - N is the time that is h hours, m minutes, and s seconds before or after T. The arithmetic is performed using the previously defined rules.

Subtracting Times

If T1 and T2 are times, the result of T1 - T2 is a time duration that gives the number of hours, minutes, and seconds between the two times. The data type of the result is DECIMAL(6,0). If T1 is greater than or equal to T2, T2 is subtracted from T1. Otherwise, T1 is subtracted from T2 and the sign of the result is negative. Given the example of T1 - T2 the rules for time subtraction are:

• If SECOND(T2) is less than or equal to SECOND(T1), the seconds part of the result is equal to SECOND(T1) - SECOND(T2).
• If SECOND(T2) is greater than SECOND(T1), the seconds part of the result is equal to 60 + SECOND(T1) - SECOND(T2), and MINUTE(T2) is incremented by one.
• If MINUTE(T2) is less than or equal to MINUTE(T1), the minutes part of the result is equal to MINUTE(T1) - MINUTE(T2).
• If MINUTE(T2) is greater than MINUTE(T1), the minutes part of the result is equal to 60 + MINUTE(T1) - MINUTE(T2), and HOUR(T2) is incremented by one.
• The hour part of the result is equal to HOUR(T1) - HOUR(T2).

Special Considerations Relating to Time Arithmetic

Adding 24 hours to the time 00.00.00 results in 24.00.00.

Adding 24 hours to any other time results in the same time.

For example, adding 24 hours to 00.00.59 results in 00.00.59.

Incrementing and Decrementing Timestamps by Durations

The result of adding or subtracting to or from a timestamp is a timestamp.

The date part of the arithmetic is performed using the rules previously defined for incrementing or decrementing a date by a date duration.

The time part of the arithmetic is performed using the rules previously defined for incrementing or decrementing a time by a time duration, except that any overflow or underflow of hours is carried into the date part of the result.

The following rules apply to microseconds:

• If S is a timestamp and N is a number, the result of S + N MICROSECONDS or S - N MICROSECONDS is the timestamp that is N microseconds before or after S.
• If S is a timestamp and N is a date duration of y years, m months, and d days, the result of S + N or S - N is the timestamp that is y years, m months, and d days before or after S.
• If S is a timestamp and N is a time duration of h hours, m minutes, and s seconds, the result of S + N or S - N is the timestamp that is h hours, m minutes, and s seconds before or after S. The microsecond part of the result is the same as the microsecond part of S.