Manipulating DATE with DATETIME and INTERVAL Values

You can use DATE operands in some arithmetic expressions with DATETIME or INTERVAL operands by writing expressions to do the manipulating, as Results of Expressions That Manipulate DATE with DATETIME or INTERVAL Values shows.
Table 1. Results of Expressions That Manipulate DATE with DATETIME or INTERVAL Values
Expression Result
DATE – DATETIME INTERVAL
DATETIME – DATE INTERVAL
DATE + or – INTERVAL DATETIME

In the cases that Results of Expressions That Manipulate DATE with DATETIME or INTERVAL Values shows, DATE values are first converted to their corresponding DATETIME equivalents, and then the expression is evaluated by the rules of arithmetic.

Although you can interchange DATE and DATETIME values in many situations, you must indicate whether a value is a DATE or a DATETIME data type. A DATE value can come from the following sources:
  • A column or program variable of type DATE
  • The TODAY keyword
  • The DATE( ) function
  • The MDY function
  • A DATE literal
A DATETIME value can come from the following sources:
  • A column or program variable of type DATETIME
  • The CURRENT keyword
  • The EXTEND function
  • A DATETIME literal

The database locale defines the default DATE and DATETIME formats. For the default locale, U.S. English, these formats are 'mm/dd/yy' for DATE values and 'yyyy-mm-dd hh:MM:ss' for DATETIME values.

To represent DATE and DATETIME values as character strings, the fields in the strings must be in the required order. In other words, when a DATE value is expected, the string must be in DATE format and when a DATETIME value is expected, the string must be in DATETIME format. For example, you can use the string 10/30/2008 as a DATE string but not as a DATETIME string. Instead, you must use 2008-10-30 or 08-10-30 as the DATETIME string.

In a nondefault locale, literal DATE and DATETIME strings must match the formats that the locale defines. For more information, see the HCL OneDB™ GLS User's Guide.

You can customize the DATE format that the database server expects with the DBDATE and GL_DATE environment variables. You can customize the DATETIME format that the database server expects with the DBTIME and GL_DATETIME environment variables. For more information, see DBDATE environment variable and DBTIME environment variable. For more information about all these environment variables, see the HCL OneDB GLS User's Guide.

You can also subtract one DATE value from another DATE value, but the result is a positive or negative INTEGER count of days, rather than an INTERVAL value. If an INTERVAL value is required, you can either use the UNITS DAY operator to convert the INTEGER value into an INTERVAL DAY TO DAY value, or else use EXTEND to convert one of the DATE values into a DATETIME value before subtracting.

For example, the following expression uses the DATE( ) function to convert character string constants to DATE values, calculates their difference, and then uses the UNITS DAY keywords to convert the INTEGER result into an INTERVAL value: 
(DATE ('5/2/2007') - DATE ('4/6/1968')) UNITS DAY

Result: INTERVAL (12810) DAY(5) TO DAY
Important: Because of the high precedence of UNITS relative to other SQL operators, you should generally enclose any arithmetic expression that is the operand of UNITS within parentheses, as in the preceding example.
If you need YEAR TO MONTH precision, you can use the EXTEND function on the first DATE operand, as the following example shows: 
EXTEND (DATE ('5/2/2007'), YEAR TO MONTH) - DATE ('4/6/1969')

Result: INTERVAL (39-01) YEAR TO MONTH

The resulting INTERVAL precision is YEAR TO MONTH, because the DATETIME value came first. If the DATE value had come first, the resulting INTERVAL precision would have been DAY(5) TO DAY.