DATE

Edit online

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



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.
    • 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.

If expression is not a DATE value, expression is cast as follows:

    • 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.

The result of the function is a date.

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 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.

    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:

The following example selects records of employees who joined later than 2019-11-10 from the table EMPLOYEE.


SELECT * FROM EMPLOYEE WHERE DATEOFJOIN > '2019-11-10'

The above example returns the following.


EMPL_NAME, AGE, DEPT, INDUSTRY, SALARY, DATEOFJOIN
BUMRAH , 53 , SALES , RETAIL , 50000 , 2019-11-25
DEV , 33 , ADMIN , RETAIL , 25000 , 2019-11-27
NEWTON , 53 , DATA , RETAIL , 75000 , 2019-11-25
SAMUEL , 43 , IT , BANKING , 75000 , 2019-11-25