The ingresdate data type is an abstract data type. The ingresdate data type input formats are as follows:

Ingresdate values are specified as quoted character strings. A date can be entered by itself or with a time value. If a date is entered without the time, no time is shown when the data displays (see How Ingres Dates and Times Are Displayed).

Because ingresdate can store different forms of date, time, and interval information, the nature of the value is determined by the input format.

Ingresdate absolute formats recognized are determined by the active date format, which can be altered to suit particular country preferences.

Ingresdate interval forms follow a simple form that is distinct from the absolute forms.

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 System Administrator Guide.

The following table lists ingresdate input and output formats:

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:

using the format mm/dd/yyyy, the DBMS Server assumes that you are referring to March 21, 2003.

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:

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

For convenience, the ANSI date/time formats are also accepted as input to ingresdates.

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.

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

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:

Ingres interval data includes date intervals, time intervals, or a combination.

An ingresdate interval value is entered as a quoted string of qualified numbers that mark the units of the interval. For example 18 months might be represented as:

The interval syntax is of the form:

where n can be a positive or negative integer. The interval qualifiers can be abbreviated:

Here are example date intervals:

Here are example time intervals:

If a time interval greater than 1 day is entered, the interval is converted to a date and time interval. For example:

is converted to:

Valid ranges for Ingres date and time intervals are:

For convenience, the ANSI interval input formats can also be used for loading ingresdate intervals.

By default ingresdate absolute values (date, time, or timestamp) are displayed as strings of left-justified 25 characters with trailing blanks.

The display format for an absolute ingresdate is determined by the II_DATE_FORMAT (see II_DATE_FORMAT for Absolute Ingresdate) or the SQL statement SET DATE_FORMAT (see Date_format).

The display format for an absolute time is:

The DBMS Server displays 24-hour times for the current time zone. Times are stored as Greenwich Mean Time (GMT) and displayed using the time zone adjustment specified by II_TIMEZONE_NAME.

If a date is not entered when entering a time, the current date is inserted in the date place. If seconds are not entered when entering a time, zeros display in the seconds place.

By default ingresdate interval is displayed as the first 25 characters of the interval string; the remainder of the interval will be truncated. The maximum length of an ingresdate interval is 57 characters.

The complete format appears as:

What is stored in the ingresdate interval determines what is displayed in the 25 characters. By default the "least significant" portion is truncated to fit the result into the 25 characters. What is deemed as "least significant" is a function of the size of any component of the time interval. Trailing blanks are appended to fill out the string to 25 characters.

For example, if the following ingresdate interval had been inserted into an ingresdate column:

when selected within terminal monitor, by default, the following would be returned:

The least significant portion of the interval, minutes and seconds, have been lost due to truncation to fit the result into 25 characters. This happens silently--there are no warnings or error messages.

To display the full string, it is necessary to cast the column to a character string of the required length. For example:

returns the full value plus trailing spaces to the length specified in the cast: