SQL/MX 3.1 Reference Manual (H06.23+, J06.12+)

SQL/MX Functions and Expressions
HP NonStop SQL/MX Release 3.1 Reference Manual—663850-001
9-21
Examples of CAST
The contents of the character string to be converted must be consistent in meaning
with the data type of the result. For example, if you are converting to DATE, the
contents of the character string must be 10 characters consisting of the year, a
hyphen, the month, another hyphen, and the day.
•
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.