Microsoft SQL Server 2008 came with a wide array of T-SQL enhancements. One of them was the ability to split a DATETIME value into DATE and TIME values. From a storage standpoint this allowed us to store and bind (on the UI) date and time values separately, while appending the values when displaying on a report or exporting to a 3rd party system. They are also useful to store time revisions with respect to a DATETIME value.
Here’s a quick example:
SQL Server 2008/2008 R2
In the query below, I will be taking two variables – a DATETIME and a TIME value and then add and subtract the TIME value from the DATETIME value to get the final result.
--Please execute on a SQL 2008/2008 R2 instance
USE tempdb;
GO
DECLARE @tDate DATETIME = '2013-02-11 00:00:00.000';
DECLARE @tTime TIME = '08:00:00.000';
SELECT @tDate AS DateTimeSource,
@tTime AS TimeSource,
(@tDate + @tTime) AS DateTimeAddResult,
(@tDate - @tTime) AS DateTimeSubtractResult;
GO
The output of the above query is shown below:
As you can see, we were able to successfully add and subtract the DATE and the TIME values to create corresponding DATETIME values.
Changes in SQL Server 2012
If your application uses code similar to the one shown above, it will not work as expected in SQL Server 2012 (SQL 11/”Denali”):
--Please execute on a SQL 2012 instance
USE tempdb;
GO
DECLARE @tDate DATETIME = '2013-02-11 00:00:00.000';
DECLARE @tTime TIME = '08:00:00.000';
SELECT @tDate AS DateTimeSource,
@tTime AS TimeSource,
(@tDate + @tTime) AS DateTimeAddResult;
SELECT @tDate AS DateTimeSource,
@tTime AS TimeSource,
(@tDate - @tTime) AS DateTimeSubtractResult;
GO
The following error message is encountered:
Msg 402, Level 16, State 1, Line 6
The data types datetime and time are incompatible in the add operator.
Msg 402, Level 16, State 1, Line 10
The data types datetime and time are incompatible in the subtract operator.
As you can see, SQL Server 2012 no longer allows us to add/subtract a TIME value from a DATETIME value using the conventional Add (+) and Subtract (-) operators.
Workaround
A workaround to this issue is to modify the code such that the TIME value is converted to a DATETIME value before the Add (+) or Subtract (-) operation takes place.
--Please execute on a SQL 2012 instance
USE tempdb;
GO
DECLARE @tDate DATETIME = '2013-02-11 00:00:00.000';
DECLARE @tTime TIME = '08:00:00.000';
SELECT @tDate AS DateTimeSource,
@tTime AS TimeSource,
(@tDate + CAST(@tTime AS DATETIME)) AS DateTimeAddResult,
(@tDate - CAST(@tTime AS DATETIME)) AS DateTimeSubtractResult;
GO
Conclusion
Between just two releases, this is a considerably major change because one may have a considerable number of objects written using this ability of DATETIME and DATE/TIME data-types to add/subtract. With an upgrade of the database to SQL Server 2012, these objects will need to be modified. I would therefore present the following questions before you:
- Have you faced this issue in your migration from SQL Server 2008 to SQL Server 2012?
- What solution/workaround did you apply?
Reference:
- DATETIME datatype: http://msdn.microsoft.com/en-us/library/ms187819.aspx
- DATE datatype: http://msdn.microsoft.com/en-us/library/bb630352.aspx
- TIME datatype: http://msdn.microsoft.com/en-us/library/bb677243.aspx
Until we meet next time,
SQLTwins by Nakul Vachhrajani 