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

weird. i copied yoru code, removed the comments, and removed the addition to the date, and it comes out fine on both of them.

    BEGIN TRAN 

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

SELECT (CONVERT(VARCHAR(50),CONVERT(DATE, d)))
FROM t 


SELECT TOP 1 (CONVERT(VARCHAR(50),CONVERT(DATE, d)))
FROM t 

ROLLBACK 

Answer 2

I can reproduce, SQL 2008 R2 x64.

TOP (n), DISTINCT, GROUP BY, and windowing functions all produce the YYYY-MM-DD date. ORDER BY does not.

Thus, I'd guess it has something to do w/ spools in tempdb, internal vs localized representations, and the automatic switching back and forth that ought to be occurring, but somehow isn't in this case.

--------------------------------  
-- these return Sep  4 2010
--------------------------------  
SELECT (CONVERT(VARCHAR(50),CONVERT(DATE, d))) + CONVERT(VARCHAR(50), '') FROM t
SELECT (CONVERT(VARCHAR(50),CONVERT(DATE, d))) + CONVERT(VARCHAR(50), '') FROM t ORDER BY 1 ASC
SELECT (CONVERT(VARCHAR(50),CONVERT(DATE, d))) + CONVERT(VARCHAR(50), '') FROM t ORDER BY 1 DESC

--------------------------------  
-- these return 2010-09-04
--------------------------------  
-- GROUP BY
SELECT c FROM (SELECT (CONVERT(VARCHAR(50),CONVERT(DATE, d))) + CONVERT(VARCHAR(50), '') FROM t) t (c) GROUP BY c
-- DISTINCT 
SELECT DISTINCT (CONVERT(VARCHAR(50),CONVERT(DATE, d))) + CONVERT(VARCHAR(50), '') FROM t
-- TOP (n)
SELECT TOP (5) (CONVERT(VARCHAR(50),CONVERT(DATE, d))) + CONVERT(VARCHAR(50), '') FROM t
-- COUNT(*) OVER ()
SELECT COUNT(*) OVER (), (CONVERT(VARCHAR(50),CONVERT(DATE, d))) + CONVERT(VARCHAR(50), '') FROM t
-- ROW_NUMBER() OVER ()
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)), (CONVERT(VARCHAR(50),CONVERT(DATE, d))) + CONVERT(VARCHAR(50), '') FROM t

Answer 3

This looks like a bug; I can reproduce on 2008 x64 developer.

I did discover something quite strange; adding another date (or datetime) to varchar cast to the query causes the formats to be normalised. So:

USE tempdb

BEGIN TRAN

declare @d date = getdate()

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

SELECT (CONVERT(VARCHAR(50),CONVERT(DATE, d))) + CONVERT(VARCHAR(50), '')
,(CONVERT(VARCHAR(50),@d))
FROM t


SELECT TOP 1 (CONVERT(VARCHAR(50),CONVERT(DATE, d))) + CONVERT(VARCHAR(50), '')
,(CONVERT(VARCHAR(50),@d))
FROM t

ROLLBACK

(note the addition of the converted variable @d to both queries) Produces:

2010-09-06  2010-09-06
2010-09-06  2010-09-06

as output.

Format 121 appears to be the default covert format for DATE whereas 0 is the default covert format for DATETIME. I wondered whether the problem is caused by the first query ignoring the innermost CONVERT, or applying it out of order?

Answer 4

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.