SQL Programming Manual for Pascal

NonStop SQL Statements and Directives
HP NonStop SQL Programming Manual for Pascal—528614-001
3-21
Inserting a Timestamp
Statements to supply values for the columns are:
BILLING_DATE := '1988-10-20';
TIME_BEFORE_PMT := '90';
{ TIME_BEFORE_PMT is 3 bytes long to provide space for a }
{ possible negative sign. You can assign any value of }
{ 3 bytes or less - for example, -90, 10, +5, 150 }
...
EXEC SQL
INSERT INTO BILLINGS VALUES
(923, DATETIME '1985-10-15' YEAR TO DAY,
:BILLING_DATE TYPE AS DATE,
:TIME_BEFORE_PMT TYPE AS INTERVAL DAY(3) );
Appendix B, Examples of Static NonStop SQL Programs also includes a program that
handles date-time and INTERVAL values.
Inserting a Timestamp
The following example shows a way to both insert a timestamp value into the database
and save it in the Pascal program, using the NonStop Kernel operating system
procedures JULIANTIMESTAMP and CONVERTTIMESTAMP and the SQL function
CONVERTTIMESTAMP. To use the NonStop Kernel operating system procedures, the
program must include the PEXTDECS file, as shown in the example.
This example inserts a timestamp value by defining column A in table T as data type
TIMESTAMP DEFAULT CURRENT. When the Pascal program inserts a row, SQL
inserts the current timestamp; however, the timestamp value inserted is not visible to
the Pascal program.
?SQL
PROGRAM TIMETEST(OUTPUT);
IMPORT BEGIN
?NOLIST
?SOURCE $SYSTEM.SYSTEM.PEXTDECS
(TYPES, JULIANTIMESTAMP,CONVERTTIMESTAMP)
?LIST
END;
EXEC SQL BEGIN DECLARE SECTION;
VAR DATE_TIME : INT64;
EXEC SQL END DECLARE SECTION;
VAR SQLCODE : INT16;
BEGIN
{ Get current Julian timestamp in greenwich mean time: }
DATE_TIME := JULIANTIMESTAMP;
{ Convert timestamp to local time: }
DATE_TIME := CONVERTTIMESTAMP(DATE_TIME);
{ Insert value into column A: }
EXEC SQL BEGIN WORK;