Home Dashboard Directory Help
Search

Converting date and time values to varchar gives seemingly unpredictable results by Martin Smith


Status: 

Closed
 as Fixed Help for as Fixed


4
0
Sign in
to vote
Type: Bug
ID: 595374
Opened: 9/6/2010 2:25:06 AM
Access Restriction: Public
1
Workaround(s)
view
2
User(s) can reproduce this bug

Description

This is based on an issue encountered by a user of the stack overflow website http://stackoverflow.com/questions/3634647/problem-with-the-use-of-top-1-in-a-query.

I found that when converting date to varchar the default conversion style seems to switch seemingly arbitrarily between 0 and 121 depending on whether TOP is added to the query plan, whether a temporary or permanent table is being used and whether it is being concatenated to the result of another conversion.

Additionally the conversion from time to varchar does not appear to stick to a consistent format either.
Details
Sign in to post a comment.
Posted by Microsoft on 9/8/2010 at 11:18 AM
Hi,

We are aware of this issue and it is confirmed to be resolved in the next release of SQL Server "Denali". However, we have decided not to change the existing behavior in previous versions of SQL Server, including SQL Server 2008 and 2008 R2, due to back-compatibility concerns for existing applications.

Thanks,
Adrian, SQL Programmability Team
Posted by Paul White NZ on 9/7/2010 at 1:02 AM
It seems that auto-parameterization is to blame for the inconsistency.

Books Online (http://msdn.microsoft.com/en-us/library/ms180878.aspx) documents that DATE, TIME, DATETIME2, and DATETIMEOFFSET use CONVERT style 121 by default, whereas style 0 is used for DATETIME and SMALLDATETIME. Someone forgot to update the auto-parameterization rules for the new types :)

Where the query can be auto-parameterized, style 0 is erroneously applied to the new DATE/TIME types if an implicit conversion, or an explicit conversion with a specified style. The query without TOP is auto-parameterized (parameter [@1] appears instead of the TIME literal). TOP is one of the (many) query features that prevents auto-parameterization.

The obvious workaround is to always specify a desired style when using CONVERT.
Sign in to post a workaround.
Posted by Paul White NZ on 9/7/2010 at 1:03 AM
Specify an explicit style when using CONVERT with the new date/time data types.