Was this helpful?
Absolute Date and Time Templates
You can specify absolute date and time templates by entering an example date that indicates exactly how you want each date and time element to be displayed or entered. You can specify any one of many variations of the following representative date and time for your date template:
Sunday, 1901 February 3 at 4:05:06 p.m.
You can use any or all parts of the representative date in your date template, as in the following examples:
d'Sun, Feb 3, 1901'
d'02/03/01 16:05:06'
d'04:05:06p'
d'3/01
An absolute date and time template overrides any date format specified by the II_DATE_FORMAT environment variable/logical. For more information on II_DATE_FORMAT, see the System Administrator Guide for the system on which your database resides.
Absolute Date and Time Template Specification
You can use the following representations of components in a date and time template, with the exceptions noted in the table that follows:
Sunday, Sun, or Su represents a day of the week
1, 01, or 1901 represents the year
2, 02, February, Feb, or Fe, represents the month
3 or 03 represent the day of the month
4, 04, or 16 (24‑hour time) represent the hour
5 or 05 represent minutes
6 or 06 represent seconds
p or p.m. represent the designations am or pm
This is easy to remember because Sunday is the first day of the week, and arguments 1, 2, 3, 4, 5 and 6 are the year, month, day, hour, minute, and second, respectively.
The following rules apply when using the representative date components to indicate position and style:
Day
You can specify the day as an ordinal number by suffixing it with the appropriate abbreviation: st, nd, rd, or th. For example, the template 3rd day of February 1901 produces a date such as 15th day of January 1998.
If input masking is on, you cannot use the full name of the day, Sunday, or ordinal numbers and their abbreviations. If you use the single digit 3 for the day, you must precede it with a space. For more information, see Input Masking for Absolute Dates.
Month
You can use either a single or double digit for the month. Use a 2 (single digit) for the month to display the months of January through September as a single digit and the months of October through December as a double digit. For example, if you specify 2 for the month, July appears as month 7 and November appears as month 11. Use 02 for the month to display or enter all months as two‑digit numbers. In this case, July appears or is entered as 07.
If input masking is on, you must precede the single digit 2 with a space in your template. You cannot use the full name of the month, February. For more information, see Input Masking for Absolute Dates.
Year
If you use the single digit 1 in the template to indicate the year, the years zero through nine are displayed or entered with only one digit; double‑digit years are displayed as two digits. For example, specifying 1 for the year displays 2004 as 4 and 2014 as 14. If you use the four digits 1901, all four digits of the year are displayed. Thus, 2004 is displayed as 2004.
If input masking is on, you must precede the single digit 1 with a space in your template. For more information, see Input Masking for Absolute Dates.
Time
You can specify 24‑hour (military) time by using 16 instead of 4 for the hour. You cannot use p or p.m. with 24‑hour time.
Order
You can arrange the arguments in various combinations in any order. For example, 03/02/01 displays first the day (3), the month (2), and the year (1), with each element separated by slashes. The date January 15, 1998 appears as 15/01/98. If your template is Sun 03/02/01, the date January 15, 1998 appears as Saturday 15/01/98. Similarly, 1901.02.03 first displays the year, the month, and the day, separated with periods, resulting in 1998.01.15.
Constant characters
Separators and other constant characters in your template, such as forward slashes (/) and periods (.), are displayed exactly as indicated in the template.
Reserved characters
If you want to use one of the special reserved template characters or symbols as an explicit character in your template, you must precede it with a backslash (\). For example, if you want to use the numeral 2 in the date template as a constant rather than as the template symbol for the month number, you must enter it as \2.
If input masking is on, you cannot use the vertical bar (|) as a special alignment character.
Ingres displays as a string constant any word you include in a date template other than the month name of February (or its allowed abbreviations), the weekday of Sunday (or its allowed abbreviations), and the time designation p or p.m.
You can combine any of the values given to specify your template, but be aware that some combinations of those values can lead to incorrect outputs. For example, you can display the numeric day of the year by specifying the day and year but leaving out the month. Therefore, 3/1901 in the template results in dates like 9/1998 for January 9, 1998, and 121/1998 for April 30, 1998. Also note that you can display just the numeric representation for month and year by leaving out the day-year and day-month. This results in outputs such as 3 for March 6, 1998, when specifying the template 02, and 98 for April 4, 1998, when specifying the template 01. However, when you use templates with 03, leaving out the month and the year, the template does not return the day of the month; instead, it returns the number of days since January 1, 1582 (Gregorian calendar), which leads to outputs like 151943 for February 1, 1998.
Numbers requiring more than one digit use up preceding blanks or zeroes. If there are no preceding blanks or zeroes left, the number expands to the right. A succeeding number does not use up a single blank immediately following a letter, word, or number. Columns of numbers can be lined up by preceding them with an appropriate number of blanks or zeroes.
Because full month and weekday names (as well as numbers without preceding blanks or zeros) are of differing lengths, date columns using these components in the format do not usually line up when displayed in columns. Follow February or Sunday with a vertical bar (|) to specify that for shorter month names or weekdays, an appropriate number of blanks is substituted for the vertical bar to line up the components. When you follow a single digit in your template with a vertical bar, each single‑digit entry is automatically preceded with a space, to line up single and double‑digit entries.
For example, the template Sunday,| February | 3,| 1901 produces dates like:
Friday,    January  15, 1998
Wednesday, May       4, 1998
Saturday,  November 20, 1998
Any character preceded by a backslash (\) is printed as it appears.
Absolute Date and Time Examples
The following table demonstrates the use of absolute date and time templates:
Format Template
Example Data
Output
d'2/3/1'
25‑oct‑1998
10/25/98
D'2/3/1'
5‑jun‑1909
6/5/9
d' 2/ 3/01'
25‑oct‑1998
10/25/98
d' 2/ 3/01'
5‑jun‑1909
6/ 5/09
d'03‑02‑01'
5‑oct‑1998
07:24:12
05‑10‑98
d'010203'
5‑oct‑1997
971005
d'1\|2\|3'
5‑oct‑1997
97|10|5
d'FEBRUARY, 1901'
1‑sep‑2134 09:13:02
SEPTEMBER 2134
d'FEBRUARY, 1901'
7‑may‑1962 13:08:42
MAY 1962
d'Sunday'
5‑oct‑1998
Wednesday
d'SUN Feb 3 16:05 1901'
13‑oct‑1998 07:24:03
THU Oct 13 07:24 1998
d'FEB 03 4:05:06 p.m.'
12‑dec‑1998 22:13:03
DEC 12 10:13:03 p.m.
d'04:05:06 PM'
5‑oct‑1998
14:08:45
02:08:45 PM
d'04:05:06 PM'
5‑oct‑1998
07:29:12
07:29:12 AM
d 16:05 pst
5‑oct‑1998
14:08:45
14:08 pst
d'3/01'
5‑oct‑1998
278/98
d'February 3rd'
29‑jul‑1954
July 29th
d'3rd day of 1901'
11‑may‑1999
131st day of 1999
Input Masking for Absolute Dates
With input masking turned on, the only valid date template is the absolute date and time template. Input masking does not allow you to use date interval templates. Other restrictions apply, as discussed in Absolute Date and Time Templates.
There must be adequate room in the date input template for all the digits required by the template. For example, you must provide enough space to enter both single‑digit and double‑digit days of the month.
To use single digits for the day, month, or year in your input template, you must do either of the following:
Precede the digit with a space to allow space for possible double‑digit entries
Be sure that the data consists only of a single digit for that component
For example, assume that you specify a format representing the month/day/year with no leading blanks or zeros in front of the digits:
d'2/3/1'
If you turn input masking on, this template is unacceptable because all of the entries can be at least two digits (00/00/00 or 00/00/0000) and the preceding format only allows for the one digit in each part. To specify a format in this manner that is acceptable when input masking is on, you can precede each single digit with a space (d' 2/ 3/ 1') or with a zero (d'02/03/01'). Specifying d' 2/ 3/ 1' causes a blank‑filled date to display in the field prior to user entry; specifying '02/03/01' displays a zero‑filled date prior to user entry.
If you specify a template that is incorrect for use with input masking and turn on input masking, the template is used, if it is otherwise correct, without activating input masking. It does not generate an error message. However, it can compare the entered data to the template only when the user attempts to exit the field. If you correct the template, and input masking is still on, input masking is automatically activated or the next data entered.
You must enter data from left to right in a date field that has input masking turned on. Only numbers in the numeric portions of the template and alphabetic characters in the non‑numeric portions (month and day names) are accepted. If you try to enter an incorrect keystroke in a position, you hear a beep and the keystroke is not entered. The entire date and time string is validated only when you exit the field.
Last modified date: 01/30/2023