DATETIME data type

The DATETIME data type stores an instant in time expressed as a calendar date and time of day.

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.

Field qualifiers to specify a DATETIME data type have this format:
DATETIME largest_qualifier TO smallest_qualifier
This resembles an INTERVAL field qualifier, but DATETIME represents a point in time, rather than (like INTERVAL) a span of time. These differences exist between DATETIME and INTERVAL qualifiers:
  • The DATETIME keyword replaces the INTERVAL keyword.
  • DATETIME field qualifiers cannot specify a nondefault precision for the largest_qualifier time unit.
  • Field qualifiers of a DATETIME data type can include YEAR, MONTH, and smaller time units, but an INTERVAL data type that includes the DAY field qualifier (or smaller time units) cannot also include the YEAR or MONTH field qualifiers.

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.)

Table 1. DATETIME field qualifiers
Qualifier field Valid entries
YEAR A year numbered from 1 to 9,999 (A.D.)
MONTH A month numbered from 1 to 12
DAY A day numbered from 1 to 31, as appropriate to the month
HOUR An hour numbered from 0 (midnight) to 23
MINUTE A minute numbered from 0 to 59
SECOND A second numbered from 0 - 59
FRACTION A decimal fraction-of-a-second with up to 5 digits of scale. The default scale is 3 digits (a thousandth of a second). For smallest_qualifier to specify another scale, write FRACTION(n), where n is the number of digits from 1 - 5.

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.)

Table 2. Delimiters used with DATETIME
Delimiter Placement in DATETIME Literal
Hyphen ( - ) Between the YEAR, MONTH, and DAY time-unit values
Blank space ( ) Between the DAY and HOUR time-unit values
Colon ( : ) Between the HOUR, MINUTE, and SECOND time-unit values
Decimal point ( . ) Between the SECOND and FRACTION time-unit values
The following illustration shows a DATETIME YEAR TO FRACTION(3) value with delimiters.
Figure 1: Example DATETIME Value with Delimiters

String "2003-09-23 12:42.06.001" is a literal value for year 2003, where "-09-23" is 23rd day of September, and "12:42.06.001" means 42 minutes, 6 seconds, and 1/1000 of a second after 12 noon. End figure description

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.

You can also enter DATETIME values as character strings. The character string must include information for each field defined in the DATETIME column. The INSERT statement in the following example shows a DATETIME value entered as a character string:
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.

The fraction field requires n digits where 1 < n < 5, rounded up to an even number. You can use the following formula (rounded up to a whole number of bytes) to calculate the number of bytes that a DATETIME value requires:
(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.

If you specify a locale other than U.S. English, the locale defines the culture-specific display formats for DATETIME values. To change the default display format, change the setting of the GL_DATETIME environment variable. When a database with a nondefault locale uses a nondefault GL_DATETIME setting, the USE_DTENV environment variable must be set to 1 before the database server can correctly process localized DATETIME values in the following operations:
  • using the LOAD or UNLOAD feature of DB-Access
  • using the dbexport or dbimport migration utilities
  • using DML statements of SQL on database tables or on objects that the CREATE EXTERNAL TABLE statement defined.

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