SQL Programming Manual for Pascal

NonStop SQL Statements and Directives
HP NonStop SQL Programming Manual for Pascal—528614-001
3-25
Handling Columns of Date-Time and INTERVAL
Types
Intervals are represented as character strings with a separator between the values of
the INTERVAL fields (year-month or day-time); an extra byte is generated at the
beginning of the INTERVAL string for a sign. This byte can contain blanks, a plus, or a
minus.
You can insert or retrieve date-time values in any of three formats, independently of the
SQL column definition. For example, you can specify formats such as 09/15/1991,
1991-09-15, or 15.09.1991. You control the display format by inserting the value in the
format you want and retrieving the value using the DATEFORMAT function. Declare
the host variable size to be consistent with the format you will use.
If you use the INVOKE directive to generate host variables from an SQL table
definition, you can specify the DATEFORMAT clause to create host variables of the
appropriate size.
The default representation for date-time and INTERVAL values appears on the
following page. If the DATEFORMAT clause on the INVOKE directive specifies
DEFAULT, a date-time column having the range of fields YEAR TO DAY is represented
as a 10-character string.
Date Representation. The column definitions in the EMPLOYEE table are:
NAME CHAR(20)
BIRTH_DATE DATETIME YEAR TO DAY
The date represented is May 28, 1952.
The invoked type in the Pascal program is:
TYPE
EMPLOYEE_TYPE = RECORD
NAME : FSTRING(20);
BIRTH_DATE : FSTRING(10);
END ;
The host variable reference is:
:BIRTH_DATE TYPE AS DATE
INTERVAL Representation. The column definition in the EMPLOYEE table is:
NAME CHAR(20)
AGE INTERVAL YEAR(2) TO MONTH
The age represented is 37 years, 11 months:
Year Separator Month Separator Day
19 52 0 5 2 8
Sign Year Separator Month
+37 11