SQL/MP Query Guide

Retrieving Data: How to Write Queries
HP NonStop SQL/MP Query Guide—524488-003
1-21
Using Date-Time Columns
This example removes the trailing blanks after FIRST_NAME and LAST_NAME and
inserts one blank between the names:
TRIM (TRAILING " " FROM FIRST_NAME || " " ||
TRIM (TRAILING " " FROM LAST_NAME)
The result is:
"ROBERT SMITH"
For more information on the TRIM function, see Removing Leading or Trailing
Characters From a String on page 1-19.
Using Date-Time Columns
SQL supports a set of date-time data types so that you can define, modify, and access
data that specifies date and time values. SQL also provides a set of date-time
functions you can use in expressions that involve columns defined with the date-time
data types.
For a complete description of date-time data types, see the SQL/MP Reference
Manual. Accessing Date-Time Values on page 1-22 describes how to access date-time
values in SQL.
Table 1-1. Date-Time Data Types
Data Type Description
DATETIME Contains a range of logically contiguous date and time fields, called
DATETIME columns, in this implied order: YEAR, MONTH, DAY, HOUR,
MINUTE, SECOND, and FRACTION.
DATE Designates a date according to the Gregorian calendar and is a synonym
for DATETIME YEAR TO DAY. A column of type DATE can contain values
that have this contiguous DATETIME fields: YEAR, MONTH, and DAY.
TIME Designates a time of day according to a 24-hour clock and is the same as
DATETIME HOUR TO SECOND. A column of type TIME can contain
values that have this contiguous DATETIME fields: HOUR, MINUTE, and
SECOND.
TIMESTAMP Designates a date according to the Gregorian calendar and a time of day
according to a 24-hour clock. TIMESTAMP is a synonym for DATETIME
YEAR TO FRACTION(6). A column of type TIMESTAMP can contain
values that have all the DATETIME fields, which are contiguous: YEAR,
MONTH, DAY, HOUR, MINUTE, SECOND, and FRACTION.
INTERVAL Contains values that designate durations of time in either year-month or
day-time intervals. The YEAR and MONTH fields of INTERVAL designate a
number of years and months. The DAY, HOUR, MINUTE, SECOND, and
FRACTION fields of INTERVAL designate a number of days, hours,
minutes, seconds, and fractions of a second.