Was this helpful?
Date Data Type
OpenSQL supports date data types for sessions connected to:
The Ingres DBMS
An Enterprise Access product to a host DBMS that supports date data types (for example, DB2 UDB, Oracle, or Rdb)
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:
Ordering on date columns
Comparing two date columns
Comparing a date column to a date constant
Absolute Date Input Formats
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.
II_DATE_FORMAT Setting
Valid Input Formats
Output
US (default format)
mm/dd/yy
mm/dd/yyyy
dd-mmm-yyyy
mm-dd-yyyy
yyyy.mm.dd
yyyy_mm_dd
mmddyy
mm-dd
mm/dd
dd-mmm-yyyy
MULTINATIONAL
dd/mm/yy
all US formats except mm/dd/yyyy
dd/mm/yy
ISO
yymmdd
ymmdd
yyyymmdd
mmdd
mdd
all US input formats except mmddyy
yymmdd
SWEDEN/FINLAND
yyyy-mm-dd
all US input formats except
mm-dd-yyyy
yyyy-mm-dd
GERMAN
dd.mm.yyyy
ddmmyy
dmmyy
dmmyyyy
ddmmyyyy
and all US input formats except yyyy.mm.dd and mmddyy
dd.mm.yyyy
YMD
mm/dd
yyyy-mm-dd
mmdd
yymdd
yymmdd
yyyymdd
yyyymmdd
yyyy-mmm-dd
yyyy-mmm-dd
DMY
dd/mm
dd-mm-yyyy
ddmm
ddmyy
ddmmyy
ddmyyyy
ddmmyyyy
dd-mmm-yyyy
dd-mmm-yyyy
MDY
mm/dd
dd-mm-yyyy
mmdd
mddyy
mmddyy
mddyyyy
mmddyyyy
mmm-dd-yyyy
mmm-dd-yyyy
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:
'03/21/00'
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:
select date('today');
To specify the current system time, use the constant now.
Absolute Time Input Formats
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.
Combined Date and Time Input
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:
Format
Example
mm/dd/yy hh:mm:ss
11/15/00 10:30:00
dd‑mmm‑yy hh:mm:ss
15‑nov‑98 10:30:00
mm/dd/yy hh:mm:ss
11/15/99 10:30:00
dd‑mmm‑yy hh:mm:ss gmt
15‑nov‑00 10:30:00 gmt
dd‑mmm‑yy hh:mm:ss [am|pm]
15‑nov‑98 10:30:00 am
mm/dd/yy hh:mm
11/15/99 10:30
dd‑mmm‑yy hh:mm
15‑nov‑00 10:30
mm/dd/yy hh:mm
11/15/98 10:30
dd‑mmm‑yy hh:mm
15‑nov‑99 10:30
Date and Time Display 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:
hh:mm:ss
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.
Last modified date: 01/30/2023