• Products >
• Support >
• Solutions
• Company
• Blog

• < Back
• ODBC drivers >
• JDBC drivers >
• Bridges, gateways >
• Other >
• All products

• < Back
• JDBC-ODBC Bridge
• JDBC-Access Gateway

• < Back
• ODBC-ODBC Bridge
• ODBC-JDBC Gateway
• dbExpress-ODBC Gateway
• XML-ODBC Server

• < Back
• ODBC-ODBC Join Engine
• SDK
• Consultancy
• Roadmap

Working with MySQL TIMESTAMP columns in SQL Server

Sometimes you need to do a little extra work when integrating two different DBMS programs. Here's a workaround we used to help a customer who was having issues when attempting to integrate SQL Server with MySQL.

The customer was getting the following error in SQL Server when working with a MySQL TIMESTAMP column.

SELECT
 *
FROM
 OPENQUERY (MYSQL, 'SELECT lastupdated FROM carriers')

Error converting data type DBTYPE_DBTIMESTAMP to datetime2.
 
The underlying reason for this was that in the customer's MySQL database, invalid DATE, DATETIME, or TIMESTAMP values were being automatically converted to zeros (for example, '0000-00-00' or '0000-00-00 00:00:00'). A zero month or day is not a valid date or time combination in SQL Server. To work around this, we first converted the column coming back from MySQL to a char(20):
 
SELECT
 *
FROM
 OPENQUERY (
 MYSQL,
 'SELECT CAST(lastupdated AS char(20) ) AS lastupdated FROM carriers'
 )
 
The column's value '0000-00-.&checktime(0000,00,00,':')' was then converted to NULL:
 
SELECT
 CASE lastupdated
 WHEN '0000-00-00 00:00:00' THEN NULL
 ELSE lastupdated
 END AS lastupdated
FROM
 openquery (
 MYSQL,
 'SELECT CAST(lastupdated AS char(20) ) AS lastupdated FROM carriers'
 )
 
Finally, to get the lastupdated column back to a datetime2, we ran:
 
SELECT
 CAST(x.lastupdated AS datetime2) AS lastupdated
FROM
 (
 SELECT
 CASE lastupdated
 WHEN '0000-00-00 00:00:00' THEN NULL
 ELSE lastupdated
 END AS lastupdated
 FROM
 openquery (
 MYSQL,
 'SELECT CAST(lastupdated AS char(20) ) AS lastupdated FROM carriers LIMIT 100'
 )
 ) x