SQL Reference

DATE

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

The schema is SYSIBM.

The DATE function returns a date from a value.

The argument must be a date, timestamp, a positive number less than or equal to 3 652 059, a valid character string representation of a date or timestamp, or a character string of length 7 that is neither a CLOB nor a LONG VARCHAR.

If the argument is a character string of length 7, it must represent 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.

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 date, timestamp, or valid string representation of a date or timestamp:
  • The result is the date part of the value.
• 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 string with a length of 7:
  • The result is the date represented by the character string.

Examples:

• Assume that the column RECEIVED (timestamp) has an internal value equivalent to '1988-12-25-17.12.30.000000'.

     DATE(RECEIVED)
  

  Results in an internal representation of '1988-12-25'.

• This example results in an internal representation of '1988-12-25'.

    DATE('1988-12-25')
  

• This example results in an internal representation of '1988-12-25'.

     DATE('25.12.1988')
  

• This example results in an internal representation of '0001-02-04'.

     DATE(35)