WebSphere Message Brokers
File: ac16750_
Writer: Bill Oppenheimer

Task topic

This build: July 31, 2007 21:18:37

Using numeric operators with datetime values

This topic provides some examples of the ESQL that you can code to manipulate datetime values with numeric operators.
Adding an interval to a datetime value
The simplest operation you can perform is to add an interval to, or subtract an interval from, a datetime value. For example, you could write the following expressions:
DATE '2000-03-29' + INTERVAL '1' MONTH
TIMESTAMP '1999-12-31 23:59:59' + INTERVAL '1' SECOND

The following example shows how to calculate a retirement date by adding the retirement age to the birth date.

DECLARE retAge CHARACTER '65';
DECLARE birthDate DATE DATE '1953-06-01';

SET OutputRoot.XML.Test.retirementDate = birthDate + CAST(retAge AS INTERVAL YEAR);

The repetition of the word DATE in the above example is intentional. The first occurrence of DATE specifies the data type of the declared variable, birthDate. The second occurrence initializes the same variable with the constant character string that is enclosed in quotes as a DATE.

Adding or subtracting two intervals
Two interval values can be combined using addition or subtraction. The two interval values must be of compatible types. It is not valid to add a year-month interval to a day-second interval as in the following example:
INTERVAL '1-06' YEAR TO MONTH + INTERVAL '20' DAY

The interval qualifier of the resultant interval is sufficient to encompass all of the fields present in the two operand intervals. For example:

INTERVAL '2 01' DAY TO HOUR + INTERVAL '123:59' MINUTE TO SECOND

results in an interval with qualifier DAY TO SECOND, because both day and second fields are present in at least one of the operand values.

Subtracting two datetime values
Two datetime values can be subtracted to return an interval. In order to do this an interval qualifier must be given in the expression to indicate what precision the result should be returned in. For example:
(CURRENT_DATE - DATE '1776-07-04') DAY

returns the number of days since the 4th July 1776, whereas:

(CURRENT_TIME - TIME '00:00:00') MINUTE TO SECOND

returns the age of the day in minutes and seconds.

Scaling intervals
An interval value can be multiplied by or divided by an integer factor:
INTERVAL '2:30' MINUTE TO SECOND / 4
Related concepts
Message flows overview
ESQL operators
Message mappings overview
Related tasks
Developing message flows
Developing ESQL
Calculating a time interval
Related reference
Built-in nodes
ESQL reference
ESQL INTERVAL data type
ESQL simple comparison operators
ESQL logical operators
ESQL numeric operators
Rules for ESQL operator precedence
CAST function
Notices | Trademarks | Downloads | Library | Support | Feedback

Copyright IBM Corporation 1999, 2007Copyright IBM Corporation 1999, 2007. All Rights Reserved.
This build: July 31, 2007 21:18:37

ac16750_ This topic's URL is: