The date data type is an abstract data type. Date values can be either absolute dates and times or time intervals.

Dates are specified as quoted character strings. You can specify a date by itself or together with a time value. If you enter a date without specifying the time, no time is displayed on output. 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 setting of II_DATE_FORMAT, 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), you can specify the last two digits of the year. The first two digits default to the current century (2000). For example, if you enter

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

In three-character month formats, for example, dd-mmm-yy, you must specify three-letter abbreviations (for example, mar, apr, may).

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

To specify the current system time, use date(now).

The legal format for inputting an absolute time is:

Input formats for absolute times are assumed to be on a 24-hour clock. If you enter a time with an am or pm designation, the DBMS Server automatically converts the time to a 24-hour internal representation.

If you omit gmt (Greenwich Mean Time), the local time zone designation is assumed. Times are stored and displayed using the time zone adjustment specified by II_TIMEZONE_NAME. If you enter an absolute time without a date, the current system date is assumed.

Any valid absolute date input format can be paired with a valid absolute time input format to form a valid date and time entry. Some examples are shown in following table, using the US absolute date input formats:

Date intervals, like absolute date values, are entered as quoted character strings. You can specify date intervals in terms of years, months, days, or combinations of these. You can abbreviate years and months to yrs and mos, respectively. For example:

The following table lists valid ranges for date intervals:

You can express time intervals as hours, minutes, seconds, or combinations of these units. (You can abbreviate time intervals to hrs, mins, or secs.) For example:

All values in an interval must be in the range -2,147,483,639 to +2,147,483,639. The DBMS Server adjusts time units as appropriate, as illustrated in the following table:

Date values are displayed as strings of 25 characters with trailing blanks inserted. To specify the output format of an absolute date and time, you must set II_DATE_FORMAT. For a list of II_DATE_FORMAT settings and associated formats, see Absolute Date Input Formats. The display format for absolute time is:

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 and adjusted for your time zone when they are displayed.

If you do not enter seconds when you enter a time, zeros are displayed in the seconds' place when that value is retrieved and displayed.

For a time interval, the DBMS Server 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

Significance is a function of the size of any component of the time interval. For instance, if you enter the following time interval:

the entry is displayed as:

truncating the minutes and seconds, the least significant portion of the time, to fit the result into 25 characters.