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