Date time arithmetic and comparisons

DATE, TIME and TIMESTAMP values may be compared with another value of the same type. Comparisons are chronological. Date time values can also be incremented, decremented, and subtracted.

If the datastore is DB2, then DB2 string representation of DATE, TIME and TIMESTAMP types can also be used. A string representation of a date or time can use ISO, USA, EUR or JIS format. A string representation of a timestamp uses ISO format.

Format Date format Date examples Time format Time examples
ISO yyyy-mm-dd 1987-02-24 1987-2-24 13.50.00 13.50
USA mm/dd/yyyy 2/24/1987 hh:mm AM or PM 1:50 pm 02:10 AM
EUR 24.02.1987 24.2.1987 13.50.00 13.55
JIS yyyy-mm-dd 1987-02-24 hh:mm:ss 13:50 13:50:05


Example 1: Date time arithmetic comparisons

e.hiredate > '1990-02-24'

The timestamp of February 24th, 1990 1:50 pm can be represented as follows

'1990-02-24-'  or 

If the datastore is DB2, DB2 decimal durations can be used in expressions and comparisons. A date duration is a decimal(8,0) number that represents the difference between two dates in the format YYYYMMDD. A time duration is a decimal(6,0) number that represents the difference between two time values as HHMMSS. A timestamp duration is a decimal(20,6) number representing the differences between two timestamp values as YYYYMMDDHHMMSS.ZZZZZZ (ZZZZZZ is the number of microseconds and is to the right of the decimal point ) .

Two date values (or time values or timestamp values) can be subtracted to yield a duration. If the second operand is greater than the first the duration is a negative decimal number. A duration can be added or subtracted from a datetime value to yield a new datetime value.


Example 2: Date time arithmetic comparisons

DATE('3/15/2000') - '12/31/1999' results in a decimal number 215 which is a duration of 0 years, 2 months and 15 days.

Durations are really decimal numbers and can be used in arithmetic expressions and comparisons.

( DATE('3/15/2000') - '12/31/1999' ) + 14 > 215 evaluates to TRUE.

DATE('12/31/1999') + DECIMAL(215,8,0) results in a date value 3/15/2000.

TIME('11:02:26') - '00:32:56' results in a decimal number 102930 which is a time duration of 10 hours, 29 minutes and 30 seconds.

TIME('00:32:56') + DECIMAL(102930,6,0) results in a time value of 11:02:26.

TIME('00:00:59') + DECIMAL(240000,6,0) results in a time value of 00:00:59.

e.hiredate + DECIMAL(500,8,0) > '2000-10-01' means compare the hiredate plus 5 months to the date 10/01/2000.


See Also

EJB query language
WHERE clause
Using EJB query