SQL/MP Query Guide

Retrieving Data: How to Write Queries
HP NonStop SQL/MP Query Guide—524488-003
1-25
Accessing Date-Time Values
The date-time qualifiers on both sides of a comparison operator must have the same
precision. If, for example, one of your columns contains a fraction value, you might
change the other literal to include the fraction column. Alternatively, you could use the
EXTEND function to adjust the range.
CONVERTTIMESTAMP Function
Suppose that the BDAY2 table contains birth dates in Julian timestamp form. This
example converts the Julian timestamp to a DATETIME value:
SELECT NAME, CONVERTTIMESTAMP (JULIAN_BDAY), HOBBIES
FROM BDAY2
WHERE NAME = "CAROLYN" ;
The query returns this result:
NAME (EXPR) HOBBIES
------- -------------------------- ---------------------
CAROLYN 1957-04-23:00:00:00.000000 GARDENING, DACHSHUNDS
--- 1 row(s) selected.
CURRENT Function
This example calls the CURRENT function and returns all rows from the PID table with
the current date:
SELECT PROJ_ID, START_DATE
FROM PID
WHERE START_DATE = CURRENT YEAR TO DAY ;
If CURRENT is called on January 29, 1992, at 11:30 PM, CURRENT YEAR TO DAY
returns all rows with a start date of 1992-01-29. The query returns this result:
PROJ_ID START_DATE
----------- ------------
5551 1992-01-29
--- 1 row(s) selected.
The date is returned in the default date format. You can specify a different format by
using the DATEFORMAT function.