我试图运行一个简单的查询,以获得所有在11月创建的行:
SELECT COUNT(*)
FROM dbo.profile
WHERE [Created] BETWEEN '2014-11-01 00:00:00.000'
AND '2014-11-30 23:59:59.997';
SMSS返回:
将varchar数据类型转换为datetime数据类型的结果。 产生了一个超范围的值。
我不明白为什么当'Created'被设置为datetime时,数据要从varchar转换到datetime:
我是否需要告诉服务器,'Created'是日期时间?如果不是,为什么我得到这个varchar信息?
编辑:数据库中的值是YYYY-MM-DD
。下面来自@SqlZim的回复说,我需要用convert()来告诉sql数据库中的日期是什么格式--并且用字母T来替换空格字符:
select count(*)
from dbo.profile
where [created] between convert(datetime,'2014-11-01T00:00:00.000')
and convert(datetime,'2014-11-30T23:59:59.997');`
我检查了你的资料,发现你在英国。如果你的sql server被设置为使用dateformat dmy,那么这就解释了你的问题。如果不使用'T'而不是日期字符串中的空格,Sql Server就不会将其识别为ISO8601格式。
试试这个:
select count(*)
from dbo.profile
where [created] between convert(datetime,'2014-11-01T00:00:00.000')
and convert(datetime,'2014-11-30T23:59:59.997');
使用日期和/或日期时间进行查询是很棘手的,为了确保你能得到你想要的东西,我建议阅读:
编辑:为了澄清,你的错误信息中的超范围值是由解释月为30,日为11而来。
我不明白为什么当'Created'被设置为datetime时,数据被从varchar转换为datetime。
你提供的用于与 "创建 "列进行比较的字词是字符串。为了将这些字词与datetime
列进行比较,SQL Server试图根据数据类型优先级的规则,将字符串转换为datetime
类型。如果没有关于字符串格式的明确信息,SQL Server将遵循其复杂的规则来解释字符串为日期时间。
在我看来,避免这类问题的最好办法就是明确类型。SQL Server为此提供了CAST和CONVERT
函数。当处理字符串和日期/时间类型时,CONVERT
是首选,因为它提供了一个样式参数来明确定义字符串的格式。
该问题使用ODBC标准(含毫秒)格式的字符串(样式121)。明确数据类型和字符串样式的结果如下:
SELECT COUNT(*)
FROM dbo.profile
WHERE [Created] BETWEEN
CONVERT(datetime, '2014-11-01 00:00:00.000', 121)
AND
CONVERT(datetime, '2014-11-30 23:59:59.997', 121);
也就是说,有很好的理由(正如Aaron在他的答案中指出的)使用半开范围而不是`BETWEEN'(我在下面使用样式120只是为了多样化):
SELECT COUNT(*)
FROM dbo.profile
WHERE
[Created] >= CONVERT(datetime, '2014-11-01 00:00:00', 120)
AND [Created] < CONVERT(datetime, '2014-12-01 00:00:00', 120);
明确类型是一个非常好的习惯,特别是在处理日期和时间的时候。
由于BETWEEN
由于不同日期/时间类型的四舍五入和其他问题而非常有问题,并且由于YYYY-MM-DD
没有尴尬的T
不是一个安全的格式,使用ISO标准的完整日期的开放式范围,没有分隔符是一个更好的方法:
WHERE Created >= '20141101' AND Created < '20141201';