Date and Time Add Incompatibility

We just found this issue in some of our stored procedures. We are upgrading our SQL Server 2008 and 2008 R2 databases to SQL 2014 when this appeared. I don’t believe that the upgrade process flagged this as an error, and since the databases were upgrading by restoring the backup files into SQL 2014 the stored procedures were not recompiled. Recompiling will reveal this error, so I suggest that you re-compile all of your stored procs and functions if you are upgrading. Here’s the error message that you will receive if you try to execute or compile:

Msg 402, Level 16, State 1, Line 6 The data types datetime and time are incompatible in the add operator.

So, what does this mean?

It means that the code is concatenating 2 values, one a date (or datetime) data type and the second a time data type. This was allowed in earlier versions of SQL Server, but the folks at Microsoft have “tightened up” the database engine here, so it’s not allowed anymore. This was done in SQL 2012, so this is the first time we are seeing it. Here’s the type of code that causes the issue:

CAST(CAST(COMN_START_DT AS DATE) AS DATETIME) + CAST(COMN_START_TM AS TIME)

 

I’ve seen this in WHERE clauses, SELECT column lists, and even in ORDER BY clauses. To correct it, CAST or CONVERT both columns to the same data type (DATETIME) and then concatenate, or CAST/CONVERT both columns to VARCHAR. Do whatever is most appropriate for your code. Like this:

CAST(COMN_END_DT + CAST(COMN_END_TM AS DATETIME) AS DATETIME)

Or this:

CAST(CAST(COMN_START_DT AS varchar) + ‘ ‘ + convert(varchar, COMN_START_TM , 8) as datetime )