DATETIME data type

You select how precisely a DATETIME value is stored; its precision can range from a year to a fraction of a second.

DATETIME stores a data value as a contiguous series of fields that represents each time unit (year, month, day, and so forth) in the data type declaration.

DATETIME largest_qualifier TO smallest_qualifier

The largest_qualifier and smallest_qualifier of a DATETIME data type can be any of the fields that the following table lists, provided that smallest_qualifier does not specify a larger time unit than largest_qualifier. (The largest and smallest time units can be the same; for example, DATETIME YEAR TO YEAR.)

The declaration of a DATETIME column need not include the full YEAR to FRACTION range of time units. It can include any contiguous subset of these time units, or even only a single time unit.

For example, you can enter a MONTH TO HOUR value in a column declared as YEAR TO MINUTE, if each entered value contains information for a contiguous series of time units. You cannot, however, enter a value for only the MONTH and HOUR; the entry must also include a value for DAY.

If you use the DB-Access TABLE menu, and you do not specify the DATETIME qualifiers, a default DATETIME qualifier, YEAR TO YEAR, is assigned.

A valid DATETIME literal must include the DATETIME keyword, the values to be entered, and the field qualifiers. You must include these qualifiers because, as noted earlier, the value that you enter can contain fewer fields than were declared for that column. Acceptable qualifiers for the first and last fields are identical to the list of valid DATETIME fields that are listed in the table DATETIME field qualifiers.

Write values for the field qualifiers as integers and separate them with delimiters. The following table lists the delimiters that are used with DATETIME values in the default US English locale. (These are a superset of the delimiters that are used in INTERVAL values.)

When you enter a value with fewer time-unit fields than in the column, the value that you enter is expanded automatically to fill all the declared time-unit fields. If you leave out any more significant fields, that is, time units larger than any that you include, those fields are filled automatically with the current values for those time units from the system clock calendar. If you leave out any less-significant fields, those fields are filled with zeros (or with 1 for MONTH and DAY) in your entry.

INSERT INTO cust_calls (customer_num, call_dtime, user_id,
       call_code, call_descr)
     VALUES (101, '2001-01-14 08:45', 'maryj', 'D',
       'Order late - placed 6/1/00');

If call_dtime is declared as DATETIME YEAR TO MINUTE, the character string must include values for the year, month, day, hour, and minute fields.

If the character string does not contain information for all the declared fields (or if it adds additional fields), then the database server returns an error.

All fields of a DATETIME column are two-digit numbers except for the year and fraction fields. The year field is stored as four digits. When you enter a two-digit value in the year field, how the abbreviated year is expanded to four digits depends on the setting of the DBCENTURY environment variable.

For example, if you enter 02 as the year value, whether the year is interpreted as 1902, 2002, or 2102 depends on the setting of DBCENTURY and on the value of the system clock calendar at execution time. If you do not set DBCENTURY, the leading digits of the current year are appended by default.

(total number of digits for all fields) /2 + 1

For example, a YEAR TO DAY qualifier requires a total of eight digits (four for year, two for month, and two for day). According to the formula, this data value requires 5, or (8/2) + 1, bytes of storage.

The USEOSTIME configuration parameter can affect the subsecond granularity when the database server obtains the current time from the operating system in SQL statements. For details, see the HCL OneDB™ Administrator's Reference.

With an ESQL API, the DBTIME environment variable affects DATETIME formatting. Nondefault locales and settings of the GL_DATE and DBDATE environment variables also affect the display of datetime data. They do not, however, affect the internal storage format of a DATETIME column.

For more information about locales and GLS environment variables that can specify end-user DATETIME formats, see the HCL OneDB GLS User's Guide.