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 HCL OneDB™ GLS User's Guide.

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

The following query uses the TO_CHAR function to convert a DATETIME value to a more readable character string.

Figure 1: Query

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

Figure 2: Query result

customer_num  119
call_date     Friday July 01 1998

The following query uses the TO_CHAR function to convert DATE values to more readable character strings.

Figure 3: Query

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

Figure 4: 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

The TO_CHAR function can also format numeric values. For more information about the built-in TO_CHAR function, see the HCL OneDB Guide to SQL: Syntax.