The SQL DATETIME and INTERVAL data types

supports two data types that can hold information about time values:

The datetime data type, which encodes an instant in time as a calendar date and a time of day.
The interval data type, which encodes a span of time.

The following table summarizes these two time data types.

Table 1. ESQL/C time data types
SQL data type	ESQL/C data type	C typedef name	Sample declaration
DATETIME	datetime	dtime_t	EXEC SQL BEGIN DECLARE SECTION; datetime year to day sale; EXEC SQL END DECLARE SECTION;
INTERVAL	interval	intrvl_t	EXEC SQL BEGIN DECLARE SECTION; interval hour to second test_num; EXEC SQL END DECLARE SECTION;

The header file datetime.h contains the dtime_t and intrvl_t structures, along with a number of macro definitions that you can use to compose qualifier values. Include this file in all C source files that use any datetime or interval host variables:

EXEC SQL include datetime;

The decimal.h header file defines the type dec_t, which is a component of the dtime_t and intrvl_t structures.

Because of the multiword nature of these data types, it is not possible to declare an uninitialized datetime or interval host variable named year, month, day, hour, minute, second, or fraction. Avoid the following declarations:

EXEC SQL BEGIN DECLARE SECTION;
   datetime year;                   /* will cause an error */
   datetime year to day year, today;                   /* ambiguous */
EXEC SQL END DECLARE SECTION;

A datetime or interval data type is stored as a decimal number with a scale factor of zero and a precision equal to the number of digits that its qualifier implies. When you know the precision and scale, you know the storage format. For example, if you define a table column as DATETIME YEAR TO DAY, it contains four digits for year, two digits for month, and two digits for day, for a total of eight digits. It is thus stored as decimal(8,0).

If the default precision of the underlying decimal value is not appropriate, you can specify a different precision. For example, if you have a host variable of type interval, with the qualifier day to day, the default precision of the underlying decimal value is two digits. If you have intervals of one hundred or more days, this precision is not adequate. You can specify a precision of three digits as follows:

interval day(3) to day;

For more information about the DATETIME and INTERVAL data types, see the HCL OneDB™ Guide to SQL: Reference.