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
See Question&Answers more detail:
os 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…