DATE

The DATE function returns a date from a value.


>>-DATE--(--expression--)--------------------------------------><

expression

An expression that returns a value of one of the following built-in data types: a date, a timestamp, a character string, a graphic string, or any numeric data type.

If expression is a character or graphic string, it must not be a CLOB or DBCLOB, and its value must be one of the following:
- A valid string representation of a date or timestamp. For the valid formats of string representations of dates and timestamps, see String representations of datetime values.
- A string with an actual length of 7 that represents a valid date in the form yyyynnn, where yyyy are digits denoting a year, and nnn are digits between 001 and 366 denoting a day of that year.
If expression is a number, it must be a positive number less than or equal to 3 652 059.

The result of the function is a date. If the argument can be null, the result can be null; if the argument is null, the result is the null value.

The other rules depend on the data type of the argument:

If the argument is a timestamp:
The result is the date part of the timestamp.
If the argument is a date:
The result is that date.
If the argument is a number:
The result is the date that is n-1 days after January 1, 0001, where n is the integral part of the number.
If the argument is a character or graphic string:
The result is the date represented by the string or the date part of the timestamp value represented by the string.

When a string representation of a date is SBCS data with a CCSID that is not the same as the default CCSID for SBCS data, that value is converted to adhere to the default CCSID for SBCS data before it is interpreted and converted to a date value.

When a string representation of a date is mixed data with a CCSID that is not the same as the default CCSID for mixed data, that value is converted to adhere to the default CCSID for mixed data before it is interpreted and converted to a date value.

When a string representation of a date is graphic data, that value is converted to adhere to the default CCSID for SBCS data before it is interpreted and converted to a date value.

Note

Syntax alternatives: The CAST specification should be used to increase the portability of applications when the argument is a date, timestamp, or character string. For more information, see CAST specification.

Examples

Assume that the column RECEIVED (TIMESTAMP) has an internal value equivalent to ‘1988-12-25-17.12.30.000000'.
```
   SELECT DATE(RECEIVED)
     FROM IN_TRAY
     WHERE SOURCE = 'BADAMSON'
```
Results in a date data type with a value of ‘1988-12-25'.
The following DATE scalar function applied to an ISO string representation of a date:
```
  SELECT DATE('1988-12-25')
    FROM SYSIBM.SYSDUMMY1
```
Results in a date data type with a value of ‘1988-12-25'.
The following DATE scalar function applied to an EUR string representation of a date:
```
  SELECT DATE('25.12.1988')
    FROM SYSIBM.SYSDUMMY1
```
Results in a date data type with a value of ‘1988-12-25'.
The following DATE scalar function applied to a positive number:
```
  SELECT DATE(35)
    FROM SYSIBM.SYSDUMMY1
```
Results in a date data type with a value of ‘0001-02-04'.