Neoview SQL Reference Manual (R2.3)

Examples of CAST
• In this example, the fractional portion is discarded:
CAST (123.956 as INTERVAL DAY(18))
• 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)
VALUES (001, 'User JBrook, executed at ' ||
CAST (CURRENT_TIMESTAMP AS CHAR(26)));
310 SQL Functions and Expressions