Microsoft distributes Microsoft SQL Server 2008 R2 Service Pack 1 (SP1) fixes as one downloadable file. Because the fixes are cumulative, each new release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2008 R2 Service Pack 1 (SP1) fix release.
Symptoms
Consider the following scenario:
-
You have a datetimeoffset data type column in a table in Microsoft SQL Server 2008 R2 or in Microsoft SQL Server 2012.
-
You create an index on the datetimeoffset column.
-
You convert the datetimeoffset data type to a smalldatetime data type by using the CAST or CONVERT function without the style argument, or when the value of the style argument is 0.
-
The result of the CAST or CONVERT function is used in a filter in a Transact-SQL query.
-
You run the Transact-SQL query.
In this scenario, the result returned by the query is incorrect because the filter does not work correctly. For example, assume that the datetimeoffset column that is named "Date" contains the following values:
-
2011-05-21 23:00:00 -01:00
-
2011-05-21 23:00:00 +00:00
-
2011-05-21 23:00:00 +01:00
You run the following query:
SELECT Date FROM TestTable WHERE CAST(Date AS smalldatetime)= '2011-05-21 23:00:00'
The result of the query returns only one row. However, the expected result should return rows for all three values.
Resolution
Cumulative update information
SQL Server 2012
The fix for this issue was first released in Cumulative Update 3 for SQL Server 2012. For more information about this cumulative update package, click the following article number to view the article in the Microsoft Knowledge Base:
2723749 Cumulative update package 3 for SQL Server 2012Note Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2012 fix release. Microsoft recommends that you consider applying the most recent fix release that contains this hotfix. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
2692828 The SQL Server 2012 builds that were released after SQL Server 2012 was released You must apply a SQL Server 2012 hotfix to an installation of SQL Server 2012.
SQL Server 2008 R2 Service Pack 2
The fix for this issue was first released in Cumulative Update 1 for SQL Server 2008 R2 Service Pack 2. For more information about how to obtain this cumulative update package, click the following article number to view the article in the Microsoft Knowledge Base:
2720425 Cumulative Update package 1 for SQL Server 2008 R2 Service Pack 2Note Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2008 R2 fix release. We recommend that you consider applying the most recent fix release that contains this hotfix. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
2730301 The SQL Server 2008 R2 builds that were released after SQL Server 2008 R2 Service Pack 2 was released
SQL Server 2008 R2 SP1
The fix for this issue was first released in Cumulative Update 7. For more information about how to obtain this cumulative update package for SQL Server 2008 R2 Service Pack 1, click the following article number to view the article in the Microsoft Knowledge Base:
2703282 Cumulative update package 7 for SQL Server 2008 R2 SP1Note Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2008 R2 Service Pack 1 fix release. We recommend that you consider applying the most recent fix release that contains this hotfix. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
2567616 The SQL Server 2008 R2 builds that were released after SQL Server 2008 R2 SP1 was released
More Information
To reproduce the issue, follow these steps:
-
Run the following query in order to create a table with a datetimeoffset column and to insert some records:CREATE TABLE #TestTimeZones (dto datetimeoffset(0)); GO INSERT INTO #TestTimeZones(dto) VALUES ('2011-05-21 23:00:00 -01:00'), ('2011-05-21 23:00:00 +00:00'), ('2011-05-21 23:00:00 +01:00'); GO
-
Run the following query, and you can see that all three values are listed correctly in the returned result:DECLARE @t smalldatetime = '2011-05-21 23:00:00' SELECT dto AS withoutIndex FROM #TestTimeZones WHERE CAST(dto AS smalldatetime)=@t;
-
Run the following query in order to create an index on the "dto" column and to repeat the same query:CREATE INDEX IX_testTimeZones_Dto ON #TestTimeZones(dto); GO -- Repeat the same query DECLARE @t smalldatetime = '2011-05-21 23:00:00' SELECT dto AS withIndex FROM #TestTimeZones WHERE CAST(dto A smalldatetime)=@t; GO
-
Run the query in step 2 again. This time, the query returns only one row.
-
Run the following query to delete the index and the table that you created:DROP INDEX IX_testTimeZones_Dto ON #TestTimeZones; GO DROP TALBE #TestTimeZones; GO
References
For more information about the CAST and CONVERT functions, go to the following MSDN website:
General information about the CAST and CONVERT functions in SQL Server
Status
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
