SQL/MP Query Guide

Retrieving Data: How to Write Queries
HP NonStop SQL/MP Query Guide—524488-003
1-27
Accessing Date-Time Values
EXTEND Function
In this example, the DAY, HOUR, MINUTE, SECOND, and FRACTION fields to the
right of MONTH are initialized to 01 (for DAY), 00 (for HOUR, MINUTE, and SECOND)
and 000000 (for FRACTION):
EXTEND ( DATETIME "1989-11" YEAR TO MONTH, YEAR TO FRACTION )
The function returns this value:
1989-11-01:00:00:00.000000
In the next example, the YEAR field to the left of MONTH is initialized to the current
year. The HOUR and MINUTE fields to the right of MONTH are initialized to 00:
EXTEND ( DATETIME "11-24" MONTH TO DAY , YEAR TO MINUTE )
In 1989, the function returns this value:
1989-11-24:00:00
The EXTEND function is especially useful when comparing DATETIME columns that
contain different fields.
You can also use the EXTEND function to shorten a DATETIME column. For example,
suppose column ATIME is defined as YEAR TO FRACTION (6) and you want to
specify grouping on YEAR TO DAY. You can specify this query:
SELECT EXTEND ( ATIME, YEAR TO DAY ) FROM TIMER GROUP BY 1 ;
JULIANTIMESTAMP Function
This example converts a DATETIME value into a Julian timestamp representation of
the value. The query selects the START_DATE column from this row in the
PROJECTS table:
PROJECT_NAME START_DATE END_DATE WAIT_TIME
------------ ---------------- ---------------- ---------
920 1988-02-21:20:30 1989-03-21:20:30 20
SELECT JULIANTIMESTAMP( START_DATE )
FROM PROJECTS
WHERE PROJECT_NAME = "920" ;
The query returns this result:
(EXPR)
------------------
211439233800000000