tsql - How does SQL Server determine the style for convert when it isn't specified?

Question

Update: This is a bug but won't get fixed until the next release of SQL Server due to backward compatibility concerns.

This is following on from this question which I answered but am still puzzled by.

Adding TOP (1) to a query is sufficient to change the result from "Sep 3 2010" to "2010-09-03" (at least on my machine with British settings) can anyone explain why? Is this a bug or is it documented somewhere?

NB: I also found in the below that if I used a #temp table then both queries returned 2010-09-03

USE tempdb

BEGIN TRAN

CREATE TABLE t (d DATETIME NOT NULL)
INSERT INTO t VALUES (GETDATE())

SELECT (CONVERT(VARCHAR(50),CONVERT(DATE, d))) + CONVERT(VARCHAR(50), '')
FROM t
/*
Returns "Sep  3 2010"

[Expr1004] = Scalar Operator(CONVERT(varchar(50),
                                     CONVERT(date,[tempdb].[dbo].[t].[d],0),
                                     0)+
                             CONVERT(varchar(50),[@1],0))
*/

SELECT TOP 1 (CONVERT(VARCHAR(50),CONVERT(DATE, d))) + CONVERT(VARCHAR(50), '')
FROM t
/*
[Expr1004] = Scalar Operator(CONVERT(varchar(50),
                                     CONVERT(date,[tempdb].[dbo].[t].[d],0),
                                     121)+
                             '')
Returns "2010-09-03"
*/

ROLLBACK

Answer 1

It seems that auto-parameterization is to blame for the inconsistency.

Books Online 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 without a specified style takes place. 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.