SQL Programming Manual for TAL

Host Variables and Parameters
HP NonStop SQL Programming Manual for TAL—527887-001
2-16
Using Host Variables
To communicate date-time values between TAL and SQL statements, you declare a
TAL STRING array as a host variable. Then you use the TYPE AS clause to cause
NonStop SQL to interpret the value in the STRING host variable as a date-time or
INTERVAL value. Some sample TYPE AS clauses are:
•
TYPE AS DATETIME YEAR TO HOUR
•
TYPE AS DATE
•
TYPE AS TIME
•
TYPE AS TIMESTAMP
•
TYPE AS INTERVAL YEAR
You can also use date-time and INTERVAL literals. For example, to insert a date-time
or INTERVAL value using a literal into TABLE:
EXEC SQL INSERT INTO table (START_DATE )
VALUES DATE "1990-03-21";
Or, to insert the value using a host variable:
! Variable declarations:
EXEC SQL BEGIN DECLARE SECTION;
STRING datevar[O:9];
EXEC SQL END DECLARE SECTION;
...
! Procedure
! Copy "1990-03-21" into datevar
...
EXEC SQL INSERT INTO table (START_DATE)
VALUES :datevar TYPE AS DATE;
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 02/15/2004,
2004-02-15, or 15.02.2004. You control the display format by inserting the value in the
Table 2-3. Date-Time and INTERVAL Data Types
Data Type Description
DATETIME Represents a date, time, or date and time, from year to microsecond
(logical subsets, such as MONTH TO DAY, are allowed)
DATE Represents a date and is a synonym for DATETIME YEAR TO DAY
TIME Represents a time and is a synonym for DATETIME HOUR TO SECOND
TIMESTAMP Represents a date and time and is a synonym for DATETIME YEAR TO
FRACTION(6)
INTERVAL Represents a duration of time as a year-month or day-time interval