DATE

The DATE function returns a date that is derived from a value.

Read syntax diagram
>>-DATE(expression)--------------------------------------------><

The schema is SYSIBM.

The argument must be an expression that returns 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 it must have one of the following values:
    • A valid string representation of a date or timestamp with an actual length that is not greater than 255 bytes. For the valid formats of string representations of dates and timestamps, see String representations of datetime values.
    • A character or graphic 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 greater than or equal to one and less than or equal to 3652059.

Start of changeIf expression is not a DATE value, expression is cast as follows:End of change

Start of change
  • If expression is a TIMESTAMP WITH TIME ZONE value, expression is cast to TIMESTAMP WITHOUT TIME ZONE, with the same precision as expression.
  • If expression is a string, expression is cast to DATE.
End of change

The result of the function is a date.

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.
  • Start of changeIf the argument is a string, the result is the date that is represented by the string. If the string contains a time zone, the time zone is ignored. If the CCSID of the string is not the same as the corresponding default CCSID at the server, the string is first converted to that CCSID.End of change

The result CCSID is the appropriate CCSID of the argument encoding scheme and the result subtype is the appropriate subtype of the CCSID.

Example 1: Assume that RECEIVED is a TIMESTAMP column in some table, and that one of its values is equivalent to the timestamp '1988-12-25-17.12.30.000000'. For this value, the following statement returns the internal representation of 25 December 1988. 
   DATE(RECEIVED)
Example 2: Assume that DATCOL is a CHAR(7) column in some table, and that one of its values is the character string '1989061'. For this value, the following statement returns the internal representation of 2 March 1989. 
   DATE(DATCOL)
Example 3: DB2® recognizes '1989-03-02' as the ISO representation of 2 March 1989. So, the following statement returns the internal representation of 2 March 1989.
   DATE('1989-03-02')