DATE Function

The DATE function converts its argument to a DATE value.

Its non-DATE argument can be any expression that can be converted to a DATE value, usually a CHAR, DATETIME, or INTEGER value. The following WHERE clause specifies a quoted string as its CHAR argument:

WHERE order_date < DATE('12/31/07')

When the DATE function interprets a CHAR non-DATE expression, it expects this expression to conform to any DATE format that the DBDATE environment variable specifies. For example, suppose DBDATE is set to Y2MD/ when you execute the following query:

SELECT DISTINCT DATE('02/01/2008') FROM ship_info;

This SELECT statement generates an error, because the DATE function cannot convert this string expression. The DATE function interprets the first part of the date string (02) as the year and the second part (01) as the month.

For the third part (2008), the DATE function encounters four digits when it expects a two-digit day (valid day values must be between 01 and 31). It therefore cannot convert the value. For the SELECT statement to execute successfully with the Y2MD/ value for DBDATE, the argument would need to be '08/02/01'. For information on the format of DBDATE, see the HCL Informix Guide to SQL: Reference.

For information on the order of precedence among Informix® environment variables that can specify the display and data entry format of DATE values, see the topic Precedence of DATE and DATETIME format specifications.

When you specify a positive INTEGER value for the non-DATE expression, the DATE function interprets this as the number of days after December 31, 1899.

If the integer value is negative, the DATE function interprets the value as the number of days before December 31, 1899. The following WHERE clause specifies an INTEGER value for the non-DATE expression:

WHERE order_date  <  DATE(365)

The database server searches for rows with an order_date value less than December 31, 1900 (which is 12/31/1899 plus 365 days).