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.