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.
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.
(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.
INTERVAL '2:30' MINUTE TO SECOND / 4