SQL/MP Programming Manual for C

Host Variables
HP NonStop SQL/MP Programming Manual for C—429847-008
2-14
Date-Time and INTERVAL Data Types
To communicate date-time or INTERVAL values between C and SQL statements,
declare a character array as a host variable and then use the TYPE AS clause to
cause NonStop SQL/MP to interpret the value as a date-time or INTERVAL value. For
the syntax of the TYPE AS clause, see Specifying Host Variables in SQL Statements
on page 2-6.
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 08/15/1996,
1996-08-15, or 15.08.1996. You control the display format by inserting the value in the
format you want and retrieving the value using the DATEFORMAT function. You must
declare the host variable size to be consistent with the format you plan to use.
This example inserts date-time values into the BILLINGS table:
EXEC SQL BEGIN DECLARE SECTION;
struct billing_rec
{
char custnum[4];
char start_date[11];
char billing_date[11];
char time_before_pmt[5];
};
struct billing_rec billings = { ' ',' ',' ',' ' };
...
EXEC SQL END DECLARE SECTION;
...
strcpy(billings.billing_date, "1996-08-20");
strcpy(billings.time_before_pmt, " 90");
...
EXEC SQL
INSERT INTO billings VALUES
("923", DATE "1985-10-15",
:billing_date TYPE AS DATE,
:time_before_pmt TYPE AS INTERVAL DAY);
...
When you invoke a column with a date-time (DATETIME, DATE, TIME, or
TIMESTAMP) or INTERVAL data type, the data is represented as a character field. The
size of the field is determined by the range of the date-time or INTERVAL column. You
control the display format by inserting the value in the format you want and retrieving
the value using the DATEFORMAT function. If you use INVOKE to generate host
variables from an SQL table definition, you can specify the DATEFORMAT clause to
determine the size.
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
Table 2-3. Date-Time and INTERVAL Data Types (page 2 of 2)