Format DATETIME values

In the following query, the EXTEND function displays only the specified subfields to restrict the two DATETIME values.

Figure 1. Query

SELECT customer_num, 
   EXTEND (call_dtime, month to minute) call_time,
   EXTEND (res_dtime, month to minute) res_time
   FROM cust_calls
   ORDER BY res_time;

The query returns the month-to-minute range for the columns labeled call_time and res_time and gives an indication of the work load.

Figure 2. Query result

customer_num call_time   res_time

         127 07-31 14:30
         106 06-12 08:20 06-12 08:25
         119 07-01 15:00 07-02 08:21
         110 07-07 10:24 07-07 10:30
         121 07-10 14:05 07-10 14:06
         116 11-28 13:34 11-28 16:47
         116 12-21 11:24 12-27 08:19

The TO_CHAR function can also format DATETIME values. See The TO_CHAR function for information about this built-in function, which can also accept DATE values or numeric values as an argument, and returns a formatted character string.

Besides the built-in time functions that these examples illustrate, HCL Informix® also supports the ADD_MONTHS, LAST_DAY, MDY, MONTHS_BETWEEN, NEXT_DAY, and QUARTER functions. In addition to these functions, the TRUNC and ROUND functions can return values that change the precision of DATE or DATETIME arguments. These additional time functions are described in the HCL Informix Guide to SQL: Syntax.