SQL/MP Query Guide

Retrieving Data: How to Write Queries
HP NonStop SQL/MP Query Guide—524488-003
1-24
Accessing Date-Time Values
Multiplying an INTERVAL Value
This expression doubles an INTERVAL value:
INTERVAL "2-7" YEAR TO MONTH * 2
The result is 5 years 2 months.
For example, suppose that you specify this query:
SELECT END_DATE + INTERVAL "2-7" YEAR TO MONTH * 2
FROM PROJECTS
WHERE PROJECT_NAME = "922" ;
The value of the END_DATE column increases by 5 years and 2 months. The query
returns this result:
(EXPR)
----------------
1996-03-20:12:30
Dividing an INTERVAL Value
This expression divides an INTERVAL value by another INTERVAL value:
INTERVAL "3" DAY / INTERVAL "2" HOUR
The result is 36. Using Date-Time Functions
Date-time functions, as well as the MAX and MIN functions, are used in expressions
that involve columns defined with the date-time data types. You can use the date-time
functions anywhere a DATETIME expression is allowed.
SQL provides the date-time functions listed in Table 1-3.
Table 1-3. Date-Time Functions
Function Description
CONVERTTIMESTAMP Converts a Julian timestamp to a DATETIME value.
CURRENT Returns the current date, current time, or current date and time.
DATEFORMAT Formats a DATETIME value.
DAYOFWEEK Returns an integer representing a day of the week.
EXTEND Adjusts the range of fields for a DATETIME value.
JULIANTIMESTAMP Returns the Julian timestamp (an operating system timestamp)
representation of a DATETIME value.
Note. SQL does not recognize an operating system timestamp as a data type. An operating
system timestamp is a LARGEINT data type that contains valid values for the
JULIANTIMESTAMP function.