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-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