SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
C-6
Examples—CAST

Multibyte datatype to any non-character datatype

Any non-character datatype to multibyte datatype
Examples—CAST

The PROJECT table contains a column START_DATE of data type DATE and a
column SHIP_TIMESTAMP of data type TIMESTAMP.
Use CAST to return the number of days for completion of a project:
SELECT projdesc, start_date, ship_timestamp,
(CAST (ship_timestamp AS DATE) - start_date) DAY
FROM persnl.project;
PROJDESC START_DATE SHIP_TIMESTAMP (EXPR)
-------------- ---------- -------------------------- ------
SALT LAKE CITY 1996-04-10 1996-04-21 08:15:00.000000 11
ROSS PRODUCTS 1996-06-10 1996-07-21 08:30:00.000000 41
MONTANA TOOLS 1996-10-10 1996-12-21 09:00:00.000000 72
AHAUS TOOL 1996-08-21 1996-10-21 08:10:00.000000 61
THE WORKS 1996-09-21 1996-10-21 10:15:00.000000 30
--- 5 row(s) selected.
DATE differences can be expressed only in the number of days, the least
significant unit of measure for dates. (An interval is either year-month or day-time.)
In this example, the result is the same if you express the difference as:
CAST (ship_timestamp AS DATE) - start_date
You are not required to specify the interval qualifier.

Suppose that your database includes a log file of user information. This example
converts the current timestamp to a character string and concatenates the result to
a character literal. Note the length must be specified.
INSERT INTO stats.logfile
(user_key, user_info)
VALUES (001, 'User JBrook, executed at ' ||
CAST (CURRENT_TIMESTAMP AS CHAR(26)));