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

Article ID: 2715289 - View products that this article applies to.
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.
Expand all | Collapse all

On This Page

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.
Back to the top | Give Feedback

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
(http://support.microsoft.com/kb/2723749/LN/ )
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
(http://support.microsoft.com/kb/2692828/LN/ )
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
(http://support.microsoft.com/kb/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
(http://support.microsoft.com/kb/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
(http://support.microsoft.com/kb/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
(http://support.microsoft.com/kb/2567616/ )
The SQL Server 2008 R2 builds that were released after SQL Server 2008 R2 SP1 was released
Back to the top | Give Feedback

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)); GOINSERT 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; GODROP TALBE #TestTimeZones; GO
Back to the top | Give Feedback

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
(http://technet.microsoft.com/en-us/library/ms187928.aspx)
Back to the top | Give Feedback

Status

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
Back to the top | Give Feedback

Properties

Article ID: 2715289 - Last Review: November 19, 2012 - Revision: 6.0
Applies to
  • Microsoft SQL Server 2008 R2 Datacenter
  • Microsoft SQL Server 2008 R2 Developer
  • Microsoft SQL Server 2008 R2 Enterprise
  • Microsoft SQL Server 2008 R2 Standard
  • Microsoft SQL Server 2008 R2 Web
  • Microsoft SQL Server 2008 R2 Workgroup
  • Microsoft SQL Server 2012 Business Intelligence
  • Microsoft SQL Server 2012 Developer
  • Microsoft SQL Server 2012 Enterprise
  • Microsoft SQL Server 2012 Standard
  • Microsoft SQL Server 2012 Web
  • SQL Server 2012 Enterprise Core
Keywords: 
kbtshoot kbqfe kbfix kbsurveynew kbexpertiseadvanced KB2715289
Back to the top | Give Feedback

Give Feedback

 
Back to the topBack to the top