带日期范围的 T-SQL 查询 [英] T-SQL query with date range
问题描述
我有一个相当奇怪的错误",有一个简单的查询,我依稀记得很久以前在某处读过它的原因,但希望有人能刷新我的记忆.
I have a fairly weird 'bug' with a simple query, and I vaguely remember reading the reason for it somewhere a long time ago but would love someone to refresh my memory.
该表是一个基本的 ID,Datetime 表.
The table is a basic ID, Datetime table.
查询是:
select ID, Datetime from Table where Datetime <= '2010-03-31 23:59:59'
问题是查询结果包括日期时间为2010-04-01 00:00:00"的结果.第二天.这是不应该的.
The problem is that the query results include results where the Datetime is '2010-04-01 00:00:00'. The next day. Which it shouldn't.
有人吗?
干杯
哞哞
推荐答案
看看 如何在 SQL Server 中存储日期? 和 在 SQL Server 中,如何使用日期?
如果这是一个 smalldatetime 它有 1 分钟的精度所以如果四舍五入,对于 datetime 它是 300 毫秒
If that is a smalldatetime it has 1 minute precision so if rounds up, for datetime it is 300 miliseconds
示例
DECLARE @d DATETIME
SELECT @d = '2001-12-31 23:59:59.999'
SELECT @d
2002-01-01 00:00:00.000
2002-01-01 00:00:00.000
DECLARE @d DATETIME
SELECT @d = '2001-12-31 23:59:59.998'
SELECT @d
2001-12-31 23:59:59.997
2001-12-31 23:59:59.997
在您的情况下,始终少于第二天午夜使用
Always use less than next day at midnight, in your case
< '20100401'
这篇关于带日期范围的 T-SQL 查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!