FIX: Incorrect results when you convert a datetimeoffset column to a smalldatetime column in SQL Server 2008 R2 or in SQL Server 2012 when there is an index on the datetimeoffset column

SQL Server 2008 R2 DatacenterSQL Server 2008 R2 DeveloperSQL Server 2008 R2 Enterprise

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 2012
Note 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 2
Note 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 SP1
Note 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:
  1. 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 
  2. 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; 
  3. 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 
  4. Run the query in step 2 again. This time, the query returns only one row.
  5. 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:

Status


Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.