SQL/MP Query Guide

Retrieving Data: How to Write Queries
HP NonStop SQL/MP Query Guide—524488-003
1-26
Accessing Date-Time Values
DATEFORMAT Function
For an example of the DATEFORMAT function, consider modifying the query used
previously for the CURRENT function:
SELECT PROJ_ID, DATEFORMAT (START_DATE, USA)
FROM PID
WHERE START_DATE = CURRENT YEAR TO DAY ;
The query returns this result. The date is now in USA format:
PROJ_ID (EXPR)
----------- ------------
5551 1992/01/29
--- 1 row(s) selected.
DAYOFWEEK Function
This example retrieves the day of the week from the date-time value in the
START_DATE column of the PROJECTS table:
SELECT project_name, start_date, DAYOFWEEK(start_date)
FROM projects
WHERE project_name = "920";
The query returns this result:
PROJECT_NAME START_DATE (EXPR)
------------ ---------------- ------
920 1988-02-21:20:30 1
--- 1 row(s) selected.
The value selected is 1, representing Sunday.
The next example retrieves the day of the week from the date-time value in the
BIRTHDATE column of the BDAY2 table:
SELECT NAME, BIRTHDATE, DAYOFWEEK(BIRTHDATE)
FROM BDAY2
WHERE NAME = "CAROLYN" ;
The query returns this result:
NAME BIRTHDATE (EXPR)
----------- ----------- ------
CAROLYN 1957-04-23 3
--- 1 row(s) selected.
The value selected is 3, representing Tuesday.