The TO_DATE function

The TO_DATE function accepts an argument of a character data type and converts this value to a DATETIME value. The TO_DATE function evaluates a character string according to the date-formatting directive that you specify and returns a DATETIME value. For a complete list of the supported date-formatting directives, see the description of the GL_DATETIME environment variable in the HCL Informix® GLS User's Guide.

You can also use the TO_DATE function to convert an LVARCHAR value to a DATETIME value.

The following query uses the TO_DATE function to convert character string values to DATETIME values whose format you specify.

Figure 1. Query

SELECT customer_num, call_descr
  FROM cust_calls
  WHERE call_dtime = TO_DATE("2008-07-07 10:24",
  "%Y-%m-%d %H:%M");

Figure 2. Query result

customer_num   110

call_descr     Order placed one month ago (6/7) not received.

You can use the DATE or TO_DATE function to convert a character string to a DATE value. One advantage of the TO_DATE function is that it allows you to specify a format for the value returned. (You can use the TO_DATE function, which always returns a DATETIME value, to convert a character string to a DATE value because the database server implicitly handles conversions between DATE and DATETIME values.)