ODBC conversions

The ODBC applications will use CHAR, DATE, TIME, or TIMESTAMP as the SQL type to bind and retrieve DATE type2 values.

The possible conversion from C datatype to SQL datatype and vice-versa are listed in the following table:

C Datatype SQL Datatype

SQL_C_TYPE_DATE or SQL_C_TYPE_TIME or SQL_C_TYPE_TIMESTAMP

SQL_CHAR

SQL_VARCHAR

SQL_LONG_VARCHAR

SQL_TYPE_DATE

SQL_TYPE_TIME

SQL_TYPE_TIMESTAMP
The possible conversions in Windows UNICODE driver are listed in the following table:
C Datatype SQL Datatype

SQL_C_TYPE_DATE or SQL_C_TYPE_TIME or SQL_C_TYPE_TIMESTAMP

SQL_WCHAR

SQL_WVARCHAR

SQL_WLONG_VARCHAR

SQL_TYPE_DATE

SQL_TYPE_TIME

SQL_TYPE_TIMESTAMP
  1. If the SQLBindParameter ODBC API C type is SQL_C_TYPE_DATE and SQL type is SQL_DATE, the date value is inserted into DATE type2 column. Because time is not part of date, the default time will be inserted into SQL/MX. For example, if the application binds 2016-11-04 from DATE column, the value inserted into SQL/MX is '2016-11-04 00:00:00 A.M.'.

  2. If the SQLBindParameter ODBC API C type is SQL_C_TYPE_TIME and SQL type is SQL_DATE, time value is inserted into DATE type2 column. Because date is not part of time, the default date will be inserted into SQL/MX. For example, if the application binds 10:58:59 for TIME column, the value inserted into SQL/MX is '2016-11-01 10:58:59 A.M.' , where 2016-11-01 represents the first day of the current month.

  3. If the SQLBindParameter ODBC API C type is SQL_C_TYPE_TIMESTAMP and SQL type is SQL_DATE, timestamp (0) value is inserted into DATE type2 column. For example, if the application binds 2016-08-23 11:38:49.23145 from TIMESTAMP column, the value inserted into SQL/MX is '2016-08-23 11:38:49 A.M.'.

  4. While retrieving results with SQLBindCol or SQLGetData ODBC API, if applications set the target type of DATE type2 column with SQL_C_TYPE_DATE to copy the DATE value into the application buffer. For example, if the inserted value is 2016-11-04 11:12:30 A.M, since the application binds with DATE, the value retrieved is '2016-11-04'.

  5. While retrieving results with SQLBindCol or SQLGetData ODBC API, if applications set the target type of DATE type2 column with SQL_C_TYPE_TIME to copy the TIME value into the application buffer. For example, if the inserted value is 2016-11-10 10:58:59 A.M, since the application binds with TIME, the value retrieved is '10:58:59'.

  6. While retrieving results with SQLBindCol or SQLGetData ODBC API, if application set the target type of DATE type2 column with SQL_C_TYPE_TIMESTAMP to copy TIMESTAMP value into the application buffer. For example, if the inserted value is 2016-08-23 10:58:59 A.M, since the application binds with TIMESTAMP, the value retrieved is '2016-08-23 10:58:59'.

JDBC conversions

The JDBC applications can use setObject(), setDate(), setString(), setTime() and setTimeStamp() methods to bind the values and getObject(),getDate(), getTime(), getTimeStamp(), and getString() methods to retrieve the values. DATE type2 is mapped to java.sql.Timestamp object in Java. If the setTime() method is called to bind the DATE type2 column, the default date value is 1970-01-01. If the setDate() method is called to bind the DATE type2 column, the default time value is 00:00:00 AM.

The ResultSetMetadata method will display the new DATE type2 column type when DC_MODE is set to 1.

Example conversions in set methods

Object Type-Example Value

Value stored in SQL/MX

java.sql.Date - 2016-09-23

2016-09-23 00:00:00

java.sql.Time - 00:21:56

1970-01-01 00:21:56

java.sql.Timestamp - 2016-09-23 17:03:50

2016-09-23 17:03:50
Example conversions in get methods

Example Value from SQL/MX

Get method - Value Displayed

2016-09-23 17:03:50

getObject() - 2016-09-23 17:03:50.0

2016-10-25 00:00:00

getDate() - 2016-10-25

2016-11-23 00:21:56

getTime() - 00:21:56

2016-09-23 17:03:50

getTimeStamp() - 2016-09-23 17:03:50.0

2016-10-25 17:10:56

getString() - 2016-10-25 17:10:56