SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Functions and Expressions
HP NonStop SQL/MX Reference Manual—540440-003
9-21
Examples of CAST
•
A date value to a character string or to a TIMESTAMP (NonStop SQL/MX fills in
the time part with 00:00:00.00).
•
A time value to a character string or to a TIMESTAMP (NonStop SQL/MX fills in the
date part with the current date).
•
A timestamp value to a character string, a DATE, a TIME, or another TIMESTAMP
with different fractional seconds precision.
•
A year-month interval value to a character string, an exact numeric, or to another
year-month INTERVAL with a different start field precision.
•
A day-time interval value to a character string, an exact numeric, or to another day-
time INTERVAL with a different start field precision.
Examples of CAST
•
This example returns the difference of two timestamps in minutes:
CAST((d.step_end - d.step_start) AS INTERVAL MINUTE)
•
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.
Note that 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)