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,
Hi Nakul,
Thanks for sharing such kind of interesting observations.
LikeLike
Thank you Nakul.
LikeLike
Hi Nakul,
Can we restore Sql server 2012 backup on Sql server 2008 R2 ?
If not, What is the solution for this ?, Client want to demigrate from Sql server 2012 to Sql server 2008 R2.
LikeLike
@Ramkoti: I moved the question to the ASK module. You can find the question and the anser here: [http://beyondrelational.com/modules/3/ask/questions/18956/how-to-restore-a-sql-server-2012-backup-to-sql-server-2008-r2-instance.aspx][1]
The question that keeps bothering me is: Why does the customer want to downgrade from SQL Server 2012 to SQL Server 2008 R2? Any specific reasons that can be shared? Knowing the reasons may help the community in general.
[1]: http://beyondrelational.com/modules/3/ask/questions/18956/how-to-restore-a-sql-server-2012-backup-to-sql-server-2008-r2-instance.aspx
LikeLike
I did this and it worked on SQL Server 2016:
CAST(T.[Date] as Datetime) + Cast(Cast(T.[Hour] as Time) as datetime)
LikeLike
Hello, Tiago! Can you please check the compatibility level of your database? If the compatibility level is 130, it would not work. For example, this does not work on a database with compatibility level 110 or higher:
DECLARE @currentDate DATETIME = GETDATE();
DECLARE @hours TIME = ’02:00:00.0000000′;
SELECT CAST(@currentDate AS DATETIME) + CAST(@hours AS TIME);
LikeLike
Pingback: SQL Server 2016 suddenly produces Error:402,State:1,Class:16 updating a sproc with DATETIME/DATE/TIME arithmetic, that was fine before