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

SQL/MX Functions and Expressions
HP NonStop SQL/MX Reference Manual—540440-003
9-67
JULIANTIMESTAMP Function
JULIANTIMESTAMP Function
The JULIANTIMESTAMP function converts a datetime value into a 64-bit Julian
timestamp value that represents the number of microseconds that have elapsed
between 4713 B.C., January 1, 00:00, and the specified datetime value.
JULIANTIMESTAMP returns a value of data type LARGEINT.
JULIANTIMESTAMP is an SQL/MX extension.
datetime-expression
is an expression that evaluates to a value of type DATE, TIME, or TIMESTAMP. If
datetime-expression does not contain all the fields from YEAR through
SECOND, NonStop SQL/MX extends the value before converting it to a Julian
timestamp. Datetime fields to the left of the specified datetime value are set to
current date fields. Datetime fields to the right of the specified datetime value are
set to zero. See Datetime Value Expressions on page 6-39.
Examples of JULIANTIMESTAMP
The PROJECT table consists of five columns using the data types NUMERIC,
VARCHAR, DATE, TIMESTAMP, and INTERVAL.
•
Convert the TIMESTAMP value into a Julian timestamp representation:
SELECT ship_timestamp, JULIANTIMESTAMP (ship_timestamp)
FROM persnl.project
WHERE projcode = 1000;
SHIP_TIMESTAMP (EXPR)
-------------------------- --------------------
1996-04-21 08:15:00.000000 211696834500000000
--- 1 row(s) selected.
•
Convert the DATE value into a Julian timestamp representation:
SELECT start_date, JULIANTIMESTAMP (start_date)
FROM persnl.project
WHERE projcode = 1000;
START_DATE (EXPR)
---------- --------------------
1996-04-10 211695854400000000
--- 1 row(s) selected.
JULIANTIMESTAMP (datetime-expression)