SQL Server Home
Converting date and time values to varchar gives seemingly unpredictable results
9/6/2010 2:25:06 AM
User(s) can reproduce this bug
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.
SQL Server 2008 - Developer Edition
Windows XP Media Center Edition
Operating System Language
Steps to Reproduce
CREATE TABLE x
INSERT INTO x values (GETDATE())
+ ' ' +
CONVERT(varchar(50), CONVERT(Time, '23:30'))) E1
SELECT top (1)
+ ' ' +
CONVERT(varchar(50), CONVERT(Time, '23:30'))) E2
Sep 6 2010 11:30PM
Adding 'TOP' to a query should not change the result of that query.
to post a comment.
Please enter a comment.
on 9/8/2010 at 11:18 AM
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.
Adrian, SQL Programmability Team
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.
to post a workaround.
Please enter a workaround.
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.
© 2014 Microsoft