未指定时，SQL Server 如何确定转换的样式? [英] How does SQL Server determine the style for convert when it isn't specified?

问题描述

更新:这是一个错误，但 这是继这个问题我已经回答了，但还是很困惑.

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

将 TOP (1) 添加到查询足以将结果从Sep 3 2010"更改为2010-09-03"(至少在我的机器上使用英国设置)任何人都可以解释为什么?这是一个错误还是记录在某处?

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?

注意:我还在下面发现，如果我使用 #temp 表，那么两个查询都返回 2010-09-03

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

推荐答案

似乎是自动参数化导致了不一致.

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

在线图书文档表明 DATE、TIME、DATETIME2 和 DATETIMEOFFSET默认情况下使用 CONVERT 样式 121，而样式 0 用于 DATETIME 和 SMALLDATETIME.有人忘记更新新类型的自动参数化规则:)

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 :)

在查询可以自动参数化的情况下，如果发生隐式转换或没有指定样式的显式转换，样式 0 会错误地应用于新的 DATE/TIME 类型.没有 TOP 的查询是自动参数化的(出现参数 [@1] 而不是 TIME 文字).TOP 是阻止自动参数化的(许多)查询功能之一.

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.

显而易见的解决方法是在使用 CONVERT 时始终指定所需的样式.

The obvious workaround is to always specify a desired style when using CONVERT.

这篇关于未指定时，SQL Server 如何确定转换的样式?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！