DATE
returns, by default, the local date in the format: dd mon
yyyy (day, month, year—for example, 25 Dec 2001
), with no
leading zero or blank on the day. Otherwise, the string input_date is
converted to the format specified by date_format1.
date_format2 can be specified to define the current format of
input_date. The default for date_format1 and
date_format2 is Normal. input_date must
not have a leading zero or blank.
You can use the following options to obtain specific date formats. (Only the bold character is
needed; all other characters are ignored.)
- Base
- the number of complete days (that is, not including the current day) since and including the
base date, 1 January 0001, in the format: dddddd (no leading zeros or
blanks). The expression
DATE('B')//7
returns a number in the range0
–6
that corresponds to the current day of the week, where0
is Monday and6
is Sunday.Thus, this function can be used to determine the day of the week independent of the national language in which you are working.
Note: The base date of 1 January 0001 is determined by extending the current Gregorian calendar backward (365 days each year, with an extra day every year that is divisible by 4 except century years that are not divisible by 400). It does not take into account any errors in the calendar system that created the Gregorian calendar originally. - Century
- the number of days, including the current day, since and including January 1 of the last year
that is a multiple of 100 in the form: ddddd (no leading zeros). Example:
A call to DATE(C) on March 13, 1992, returns 33675, the number of days from 1 January 1900 to 13
March 1992. Similarly, a call to DATE(C) on November 20, 2001, returns 690, the number of days from
1 January 2000 to 20 November 2001. Note: When used for date_format1, this option is valid when input_date is not specified.
- Days
- the number of days, including the current day, so far in this year in the format: ddd (no leading zeros or blanks).
- European
- date in the format: dd/mm/yy
- Julian
- date in the format: yyddd. Note: When used for date_format1, this option is valid only when input_date is not specified.
- Month
- full English name of the current month, in mixed case—for example,
August
. Only valid for date_format1. - Normal
- date in the format: dd mon yyyy, in mixed case. This is
the default. If the active language has an abbreviated form of the month name, then it is
used—for example,
Jan
,Feb
, and so on. If Normal is specified (or allowed to default) for date_format2, the input_date must have the month (mon) specified in the English abbreviated form of the month name in mixed case. - Ordered
- date in the format: yy/mm/dd (suitable for sorting, and so forth).
- Standard
- date in the format: yyyymmdd (suitable for sorting, and so forth).
- Usa
- date in the format: mm/dd/yy.
- Weekday
- the English name for the day of the week, in mixed case—for example,
Tuesday
. Only valid for date_format1.
Here are some examples, assuming today is
November 20, 2001
: DATE() -> '20 Nov 2001'
DATE(,'20020609','S') -> '9 Jun 2002'
DATE('B') -> '730808'
DATE('B','25 Sep 2001') -> '730752'
DATE('C') -> '690'
DATE('E') -> '20/11/01'
DATE('J') -> '01324'
DATE('M') -> 'November'
DATE('N') -> '20 Nov 2001'
DATE('N','1438','C') -> '8 Dec 2003'
DATE('O') -> '01/11/20'
DATE('S') -> '20011120'
DATE('U') -> '11/20/01'
DATE('U','25 May 2001') -> '05/25/01'
DATE('U','25 MAY 2001') -> ERROR,month not in mixed case
DATE('W') -> 'Tuesday'
Note:
- The first call to DATE or TIME in one clause causes a time stamp to be made that is then used for all calls to these functions in that clause. Therefore, multiple calls to any of the DATE or TIME functions or both in a single expression or clause are guaranteed to be consistent with each other.
- Input dates given in 2-digit year formats (i.e. European, Julian, Ordered,
Usa) are interpreted as being within a 100 year window as calculated by:
(current_year - 50) = low end of window
(current_year + 49) = high end of window
DATE conversion requires that the input_date conforms exactly to one of the syntax forms that
could have been output by the DATE function. For example, the following invocations of DATE would
fail. The incorrect specification of input_date in each case would result
in the same error message, IRX0040I.
DATE('B','7 MAY 2001') -> IRX0040I (input is not mixed case)
DATE('B',' 7 May 2001') -> IRX0040I (input has a leading blank)
DATE('B','07 May 2001') -> IRX0040I (input has a leading zero)