Was this helpful?
Date Data Type
The date data type is an abstract data type. Date values can be either absolute dates and times or time intervals.
Absolute Date Input Formats
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.
II_DATE_FORMAT Setting
Valid Input Formats
Output
US (default format)
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/yyyy
and all US formats except mm/dd/yyyy
dd/mm/yy
ISO
yymmdd
ymmdd
yyyymmdd
mmdd
mdd
and 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
mm-dd-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), 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
"03/21/03"
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:
retrieve (tdate=date("today"))
To specify the current system time, use date(now).
Absolute Time Input Formats
The legal format for inputting an absolute time is:
hh:mm[:ss] [am|pm] [gmt]
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.
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. Some examples are shown in following table, 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 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:
"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
-119977 to +119977
Days
-3652047 to +3652047
Time Interval Formats
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:
"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. The DBMS Server adjusts time units as appropriate, as illustrated in the following table:
Value entered
Value displayed
3601 seconds
1 hrs 1 secs
61 minutes
1 hrs 1 mins
26 hours
1 day 2 hours
Date and Time Display Formats
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:
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 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
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 you enter the following time interval:
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 minutes and seconds, the least significant portion of the time, to fit the result into 25 characters.
Last modified date: 01/30/2023