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

Status : 

  Fixed<br /><br />
		This item has been fixed in the current or upcoming version of this product.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


4
0
Sign in
to vote
ID 595374 Comments
Status Closed Workarounds
Type Bug Repros 2
Opened 9/6/2010 2:25:06 AM
Access Restriction Public

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.
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.