Date-Conversion Functions (IDS)

The DATE function converts a character string to a DATE value. In Figure 200, the DATE function converts a character string to a DATE value to allow for comparisons with DATETIME values. The query retrieves DATETIME values only when call_dtime is later than the specified DATE.

Figure 200. Query

SELECT customer_num, call_dtime, res_dtime
   FROM cust_calls
   WHERE call_dtime > DATE ('12/31/97')

Figure 201. Query Result

customer_num call_dtime       res_dtime

         106 1998-06-12 08:20 1998-06-12 08:25
         110 1998-07-07 10:24 1998-07-07 10:30
         119 1998-07-01 15:00 1998-07-02 08:21
         121 1998-07-10 14:05 1998-07-10 14:06
         127 1998-07-31 14:30

Figure 202 converts DATETIME values to DATE format and displays the values, with labels, only when call_dtime is greater than or equal to the specified date.

Figure 202. Query

SELECT customer_num,
   DATE (call_dtime) called, 
   DATE (res_dtime) resolved
   FROM cust_calls
   WHERE call_dtime >= DATE ('1/1/98')

Figure 203. Query Result

customer_num called       resolved

         106 06/12/1998   06/12/1998
         110 07/07/1998   07/07/1998
         119 07/01/1998   07/02/1998
         121 07/10/1998   07/10/1998
         127 07/31/1998

Using the TO_CHAR Function

The TO_CHAR function converts DATETIME or DATE values to character string values. The TO_CHAR function evaluates a DATETIME value according to the date-formatting directive that you specify and returns an NVARCHAR value. For a complete list of the supported date-formatting directives, see the description of the GL_DATETIME environment variable in the IBM Informix: GLS User's Guide.

You can also use the TO_CHAR function to convert a DATETIME or DATE value to an LVARCHAR value.

Figure 204 uses the TO_CHAR function to convert a DATETIME value to a more readable character string.

Figure 204. Query

SELECT customer_num, 
   TO_CHAR(call_dtime, "%A %B %d %Y") call_date 
   FROM cust_calls
   WHERE call_code = "B"

Figure 205. Query Result

customer_num  119
call_date     Friday July 01 1998

Figure 206 uses the TO_CHAR function to convert DATE values to more readable character strings.

Figure 206. Query

SELECT order_num, 
   TO_CHAR(ship_date,"%A %B %d %Y") date_shipped
   FROM orders
   WHERE paid_date IS NULL

Figure 207. Query Result

order_num            1004 
date_shipped         Monday May 30 1998

order_num            1006
date_shipped

order_num            1007
date_shipped         Sunday June 05 1998

order_num            1012
date_shipped         Wednesday June 29 1998

order_num            1016
date_shipped         Tuesday July 12 1998

order_num            1017
date_shipped         Wednesday July 13 1998

Using 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 IBM Informix: GLS User's Guide.

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

Figure 208 uses the TO_DATE function to convert character string values to DATETIME values whose format you specify.

Figure 208. Query

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

Figure 209. 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.)

Figure 210 uses the TO_DATE function to convert character string values to DATE values whose format you specify.

Figure 210. Query

SELECT order_num, paid_date
   FROM orders
   WHERE order_date = TO_DATE("6/7/98", "%m/%d/%iY")

Figure 211. Query Result

order_num    paid_date 

1008         07/21/1998