Expressions with Date and Time (SQL)

You can add or substract an integer number (as an interval) from values of DATE, TIME and TIMESTAMP types. You can also count the numerical difference of two values of the same type and get an integer value.

The interval specifies the number of days for DATE type, number of thousandths of seconds for TIME type and number of seconds for TIMESTAMP type (e.g. You can have an interval as a result of two TIME type values substraction. To add this to a TIMESTAMP type value you must divide it by one thousand first).

If the first operand in a expression is a literal of TIME or TIMESTAMP types, you must specify at least one blank space before the + or - operator, so the addition or subtraction is not mistaken as the time zone entry. The first plus in the following example is a time zone specification, the second is the operator for "adding a thousandth of a second".

SET tm = 12:24:00+02 + 30*1000;

602SQL Variances

Usage of an integer type instead of the special INTERVAL type is specific to 602SQL.

Example:

Select invoices from the last ten days using the CURRENT_DATE function to get the date.

SELECT number,date1
FROM Invoices
WHERE date1 BETWEEN CURRENT_DATE-10 AND CURRENT_DATE

String Expressions

Expressions

Rounding Numbers in SQL