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	hh.mm.ss	13.50.00 13.50
USA	mm/dd/yyyy	2/24/1987	hh:mm AM or PM	1:50 pm 02:10 AM
EUR	dd.mm.yyyy	24.02.1987 24.2.1987	hh.mm.ss	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-13.50.00.000000'  or 
'1990-02-24-13.50.00'

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
Expressions

 

Related Tasks

Using EJB query

 

---