Language Reference Guide : Language Elements : Data Types : Date/Time Data Types
 
Share this page          
Date/Time Data Types
Date/time data types include only date data types.
Date/Time Input Formats
Date/time data values have the following data type input formats:
Ingresdate
Date
Note:  Date and Ingresdate may be used interchangeably throughout this document because OpenROAD only supports Ingres dates. If an ansidate column is used in the database, OpenROAD will coerce it to date(ingresdate).
Date Data Type
The declaration format of the date type can be one of the following:
DATE
INGRESDATE
Examples:
Date Format
Example
DATE
The keyword DATE is used for a column data type of INGRESDATE.
INGRESDATE
For more information, see Date(Ingresdate) Data Types.
Date(Ingresdate) Data Types
The date(ingresdate) data type is an abstract data type. Date(ingresdate) values can contain either absolute dates and times or time intervals. The date(ingresdate) data type input formats are as follows:
Absolute date input
Absolute time input
Combined date and time input
Date interval
Time interval
Absolute Date Input
Date(ingresdate) values are specified as quoted character strings. A date can be entered by itself or together with a time value. If a date is entered without specifying the time, no time is shown when the data displays (see Date and Time Display).
II_DATE_FORMAT for Ingresdate
The II_DATE_FORMAT setting determines the legal formats for absolute ingresdate values. The default setting is US. II_DATE_FORMAT can be set on a session basis. For information on setting II_DATE_FORMAT, see the Workbench User Guide.
The following table lists date input and output formats:
II_DATE_FORMAT Setting
Valid Input Formats
Output
US (default)
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
and all US formats
except mm/dd/yyyy
dd/mm/yy
MULTINATIONAL4
dd/mm/yyyy
and all US formats
dd/mm/yyyy
ISO
yymmdd
ymmdd
yyyymmdd
mmdd
mdd
and all US input formats except mmddyy
yymmdd
ISO4
yymmdd
ymmdd
yyyymmdd
mmdd
mdd
and all US input formats except mmddyy
yyyymmdd
SWEDEN or 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
yymmd
d
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 this date is entered:
'03/21/09'
using the format mm/dd/yyyy, OpenROAD assumes that you are referring to March 21, 2009.
In three-character month formats, for example, dd-mmm-yy, specify three-letter abbreviations for the month (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 date and time, use the constant, now.
II_DATE_CENTURY_BOUNDARY
The II_DATE_CENTURY_BOUNDARY variable, which can be set to an integer in the 0< n <=100 range, dictates the implied century for an ingresdate value when only the last two digits of the year are entered.
For example, if II_DATE_CENTURY_BOUNDARY is 50 and the current year is 1999, an input date of 3/17/51 is treated as March 17, 1951, but a date of 03/17/49 is treated as March 17, 2049.
If the II_DATE_CENTURY_BOUNDARY variable is not set or if it is set to 0 or 100, the current century is used. If the user enters the full four digits for the year in a four-digit year field in the application, the year is accepted as entered, regardless of the II_DATE_CENTURY_BOUNDARY setting.
Absolute Time Input
The legal format for inputting an absolute time into an ingresdate value is:
hh:mm[:ss] [am|pm] [timezone]
Input formats for absolute times are assumed to be on a 24-hour clock. If a time with the designation am or pm is entered, the time is converted to a 24-hour internal and displayed representation.
If timezone is omitted, the local time zone designation is assumed. Times are stored as Greenwich Mean Time (GMT) and displayed using the time zone adjustment specified by II_TIMEZONE_NAME.
If an absolute time without a date is entered, the date defaults to 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 in an ingresdate. The following table shows examples of valid date and time entries, using the US absolute date input formats:
Format
Example
mm/dd/yy hh:mm:ss
11/15/03 10:30:00
dd-mmm-yy hh:mm:ss
15-nov-03 10:30:00
mm/dd/yy hh:mm:ss
11/15/03 10:30:00
dd-mmm-yy hh:mm:ss gmt
15-nov-03 10:30:00 gmt
dd-mmm-yy hh:mm:ss [am|pm]
15-nov-03 10:30:00 am
mm/dd/yy hh:mm
11/15/03 10:30
dd-mmm-yy hh:mm
15-nov-03 10:30
mm/dd/yy hh:mm
11/15/03 10:30
dd-mmm-yy hh:mm
15-nov-03 10:30
Date Interval
Date(ingresdate) interval values, like absolute date values, are entered as quoted character strings. Date intervals can be specified in terms of years, months, days, or combinations of these. Years and months can be abbreviated to yrs and mos, respectively. For example:
'5 years'
'8 months'
'14 days'
'5 yrs 8 mos 14 days'
'5 years 8 months'
'5 years 14 days'
'8 months 14 days'
The following table lists valid ranges for date intervals:
Date Interval
Range
Years
-9999 to +9999
Months
-119988 to +119988
Days
-3652047 to +3652047
Time Interval
Time intervals can be specified as hours, minutes, seconds, or combinations of these units. They can be abbreviated to hrs, mins, or secs. For example:
'23 hours'
'38 minutes'
'53 seconds'
'23 hrs 38 mins 53 secs'
'23 hrs 53 seconds'
'28 hrs 38 mins'
'38 mins 53 secs'
'23:38 hours'
'23:38:53 hours'
All values in an interval must be in the range -2,147,483,639 to +2,147,483,639. If a time interval greater than 1 day is entered, the interval is converted to a date and time interval.
For example:
'26 hours'
is converted to:
'1 day 2 hours'
Date and Time Display
Date(ingresdate) values display as strings of 25 characters with trailing blanks inserted.
To specify the output format of an absolute date and time, set II_DATE_FORMAT. For a list of II_DATE_FORMAT settings and associated formats, see Absolute Date Input. The display format for absolute time is:
hh:mm:ss
The DBMS Server displays 24-hour times for the current time zone, which is determined when Ingres is installed. Dates are stored in Greenwich Mean Time (GMT) and adjusted for your time zone when they are displayed.
If seconds are not entered when entering a time, zeros display in the seconds place.
For a time interval, Ingres displays the most significant portions of the interval that fit in the 25-character string. If necessary, trailing blanks are appended to fill out the string. The format appears as:
yy yrs mm mos dd days hh hrs mm mins ss secs
Significance is a function of the size of any component of the time interval. For instance, if the following time interval is entered:
'5 yrs 4 mos 3 days 12 hrs 32 min 14 secs'
the entry is displayed as:
'5 yrs 4 mos 3 days 12 hrs'
Truncating the least significant portion of the time (the minutes and seconds) to fit the result into 25 characters.
Coercion Between Date/Time Data Types
OpenROAD 6.2 supports the Ingres DATE (INGRESDATE or DATE) data type. OpenROAD Runtime supports coercion of various Ingres ANSI INTERVAL, ANSI TIME, ANSI DATE, and ANSI TIMESTAMP columns into OpenROAD variables declared as DATE. OpenROAD can select, update, and insert INGRESDATE, ANSI DATE, ANSI TIME, and ANSI TIMESTAMP string literals to the target DBMS. It is up to the target DBMS to perform coercion. If a coercion into the OpenROAD DATE variable is not supported, the coercion into a string literal can be supported.
Note:  When the OpenROAD runtime coerces an INGRES TIMESTAMP to an INGRESDATE, the milliseconds in the INGRES TIMESTAMP value are truncated. When the INGRESDATE is coerced into an INGRES TIMESTAMP by the Ingres DBMS, the INGRES TIMESTAMP is created without millisecond precision.