Data Types

The topic SQL/COBOL Data Type Mappings includes a table which shows the mappings used by OpenESQL when converting between SQL and COBOL data types.

The format of an ODBC date is yyyy-mm-dd, and an ODBC time is hh:mm:ss. These may not correspond to the native date/time formats for the data source in use. For input character host variables, native data source date/time formats can be used. For most data sources, we recommend a picture clause of PIC X(29), for example:

 01  mydate      PIC x(29).
 ...
     EXEC SQL
         INSERT INTO TABLE1 VALUES (1,'1997-01-24 12:24')
     END-EXEC
 ...
     EXEC SQL
         SELECT DT INTO :mydate FROM TABLE1 WHERE X = 1
     END-EXEC
     display mydate

Alternatively, you can use ODBC escape sequences. ODBC defines escape sequences for date, time and timestamp literals. These escape sequences are recognized by ODBC drivers which replace them with data source specific syntax.

The escape sequences for date, time and timestamp literals take the form:

date	{d '`yyyy`-`mm`-`dd`'}
time	{t '`hh`:`mm`:`ss`'}
timestamp	{ts `yyyy`-`mm`-`dd` `hh`:`mm`:`ss`[.f...]

The example program below shows date, time and timestamp escape sequences being used:

 working-storage section.
 EXEC SQL INCLUDE SQLCA END-EXEC

 01  date-field1      pic x(29).
 01  date-field2      pic x(29).
 01  date-field3      pic x(29).

 procedure division.
     EXEC SQL
        CONNECT TO 'Net Express 4.0 Sample 1' USER 'admin'
     END-EXEC
* If the Table is there drop it.
     EXEC SQL
        DROP TABLE DT
     END-EXEC

* Create a table with columns for DATE, TIME, and DATE/TIME
* NOTE:  Access uses DATETIME column for all three.
*        Some databases will have dedicated column types.
* If you are creating DATE/TIME columns on another data 
* source, refer to your database documentation to see how to
* define the columns.

     EXEC SQL
        CREATE TABLE DT ( id  INT,
                        myDate DATE NULL,
                        myTime TIME NULL,
                        myTimestamp TIMESTAMP NULL)
     END-EXEC

* INSERT into the table using the ODBC Escape sequences

     EXEC SQL
        INSERT into DT values (1 ,
            {d '1961-10-08'},  *> Set just the date part
            {t '12:21:54'  },  *> Set just the time part
            {ts '1966-01-24 08:21:56' } *> Set both parts
                              )
     END-EXEC

* Retrieve the values we just inserted
   
     EXEC SQL
        SELECT myDate
              ,myTime
              ,myTimestamp
         INTO  :date-field1
              ,:date-field2
              ,:date-field3
         FROM DT
         where id = 1
     END-EXEC

* Display the results.

     display 'where the date part has been set :' 
             date-field1
     display 'where the time part has been set :' 
             date-field2
     display 'NOTE, most data sources will set a default '
             'for the date part '
     display 'where both parts has been set :' 
             date-field3

* Remove the table.
   
     EXEC SQL
        DROP TABLE DT
     END-EXEC

* Disconnect from the data source

     EXEC SQL
        DISCONNECT CURRENT
     END-EXEC

     stop run.

Alternatively you can use host variables defined with SQL TYPEs for date/time variables. Define the following host variables:

01  my-id          pic s9(08) COMP-5.
01  my-date        sql type is date.
01  my-time        sql type is time.
01  my-timestamp   sql type is timestamp.

and replace the INSERT statement with the following code:

*> INSERT into the table using SQL TYPE HOST VARS
    move 1                           to  MY-ID
    move "1961-10-08"                to  MY-DATE
    move "12:21:54"                  to  MY-TIME
    move "1966-01-24 08:21:56"       to  MY-TIMESTAMP

     EXEC SQL
        INSERT into DT value (
          :MY-ID
         ,:MY-DATE
         ,:MY-TIME
         ,:MY-TIMESTAMP  )
     END-EXEC