OpenSQL supports date data types for sessions connected to:

If the host DBMS supports date data types, the iidbcapabilities standard catalog table includes a row where cap_capability is set to OPEN_SQL_DATES, and cap_value is set to LEVEL 1.

Tables created in OpenSQL with date columns are mapped to the date format of the host DBMS. For example, OpenSQL date would map to Rdb date and to IBM timestamp.

On input, date constants in queries must be specified using the OpenSQL date() function.

OpenSQL supports the following operations on date data:

Dates are specified as quoted character strings. A date can be entered by itself or together with a time value. For more information about date and time display, see Date and Time Display Formats.

The legal formats for absolute date values are determined by the II_DATE_FORMAT setting, summarized in the following table. If II_DATE_FORMAT is not set, the US formats are the default input formats. II_DATE_FORMAT can be set on a session basis. For information on setting II_DATE_FORMAT, see the System Administrator Guide.

Year defaults to the current year. In formats that include delimiters (such as forward slashes or dashes), specify the last two digits of the year. The first two digits default to the current century (2000). For example, if you enter the following date:

using the format mm/dd/yyyy, OpenSQL assumes that you are referring to March 21, 2000.

In three‑character month formats, for example, dd‑mmm‑yy, OpenSQL requires three‑letter abbreviations (for example, mar, apr, may).

To specify the current system date, use the constant today. For example:

To specify the current system time, use the constant now.

The legal format for inputting an absolute time is

'hh:mm[:ss] [am|pm] [timezone]'

Input formats for absolute times are assumed to be on a 24‑hour clock. If a time is entered with an am or pm designation, then OpenSQL automatically converts the time to a 24‑hour internal representation.

If timezone is omitted, OpenSQL assumes the local time zone designation. Times are displayed using the time zone adjustment specified by II_TIMEZONE_NAME. For details about time zone settings and valid time zones, see the Getting Started guide.

If an absolute time is entered without a date, OpenSQL assumes the current system date.

Any valid absolute date input format can be paired with a valid absolute time input format to form a valid date and time entry. The following table shows some examples of valid date and time entries using the US absolute date input formats:

OpenSQL outputs date values as strings of 25 characters with trailing blanks inserted.

To specify the output format of an absolute date and time, II_DATE_FORMAT must be set. For a list of II_DATE_FORMAT settings and associated formats, see Absolute Date Input Formats. The display format for absolute time is:

OpenSQL displays 24‑hour times for the current time zone, which is determined when OpenSQL is installed. Dates are stored in Greenwich Mean Time (GMT) and adjusted for your time zone when they are displayed.

If seconds are omitted when entering a time, OpenSQL displays zeros in the seconds' place.