T-SQL 查询返回不应该返回的项目

发布于 2024-08-06 04:57:39 字数 432 浏览 4 评论 0原文

这是场景。有问题的列称为“datein”，其类型为“datetime”。我有三行“datein”的值为“2009-10-01 00:00:00.000”。为什么这个查询返回上述行？

SELECT  *
FROM    t_call AS tc
WHERE   tc.datein >= '2009-09-30 00:00:00.000'
        AND tc.datein <= '2009-09-30 23:59:59.999'

使用

SELECT  *
FROM    t_call AS tc
WHERE   tc.datein BETWEEN '2009-09-30 00:00:00.000'
        AND '2009-09-30 23:59:59.999'

返回相同的结果

原文

Here's the scenario. The column in question is called 'datein' and it's type is 'datetime'. I have three rows with the value of '2009-10-01 00:00:00.000' for 'datein'. Why does this query return the aforementioned rows?

SELECT  *
FROM    t_call AS tc
WHERE   tc.datein >= '2009-09-30 00:00:00.000'
        AND tc.datein <= '2009-09-30 23:59:59.999'

Using

SELECT  *
FROM    t_call AS tc
WHERE   tc.datein BETWEEN '2009-09-30 00:00:00.000'
        AND '2009-09-30 23:59:59.999'

returns the same result

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

一世旳自豪 2024-08-13 04:57:40

列类型是什么？如果它是日期时间，请尝试将要比较的值也转换为日期时间；如果它是字符串（char、nchar、varchar、nvarchar），您对该列使用什么排序规则等？

回复收藏 0 原文

半边脸i 2024-08-13 04:57:40

根据 MSDN 文档

1 月 1 日起的日期和时间数据，
1753 年至 9999 年 12 月 31 日，
准确度为百分之三
第二（相当于 3.33
毫秒或 0.00333 秒）。
值四舍五入为增量
.000、.003 或 .007 秒，如图所示
在表中。

根据该页面中给定的示例，您必须以 .997 结束查询才能获得您期望的结果。

回复收藏 0 原文

蓝梦月影 2024-08-13 04:57:40

编写这些查询的安全方法如下：

SELECT  *
FROM    t_call AS tc
WHERE   tc.datein >= '2009-09-30T00:00:00.000'
        AND tc.datein < '2009-10-01T00:00:00.000''

The safe way to write these queries is as follows:

SELECT  *
FROM    t_call AS tc
WHERE   tc.datein >= '2009-09-30T00:00:00.000'
        AND tc.datein < '2009-10-01T00:00:00.000''

回复收藏 0 原文

挽清梦 2024-08-13 04:57:39

这是缺乏千分之一秒值的精度。请尝试使用“.997”。

MSDN DateTime 文档

运行此命令，您将看到:

declare @dt datetime

select @dt = '2009-09-30 23:59:59.999'
select @dt

It's the lack of precision in the thousandths of a second value. Try ".997" instead.

MSDN DateTime documentation

Run this and you'll see:

declare @dt datetime

select @dt = '2009-09-30 23:59:59.999'
select @dt

回复收藏 0 原文

街道布景 2024-08-13 04:57:39

DATETIME 精度为 0.00333 秒。因此，您需要转到“2009-09-30 23:59:59.998”，这样它就不会四舍五入到 10 月 1 日。

例如：

select '2009-09-30 23:59:59.994', 
  cast('2009-09-30 23:59:59.994' as datetime)
union all select '2009-09-30 23:59:59.995', 
  cast('2009-09-30 23:59:59.995' as datetime)
union all select '2009-09-30 23:59:59.996', 
  cast('2009-09-30 23:59:59.996' as datetime)
union all select '2009-09-30 23:59:59.997', 
  cast('2009-09-30 23:59:59.997' as datetime)
union all select '2009-09-30 23:59:59.998', 
  cast('2009-09-30 23:59:59.998' as datetime)
union all select '2009-09-30 23:59:59.999', 
  cast('2009-09-30 23:59:59.999' as datetime)

2009-09-30 23:59:59.994 2009-09-30 23:59:59.993
2009-09-30 23:59:59.995 2009-09-30 23:59:59.997
2009-09-30 23:59:59.996 2009-09-30 23:59:59.997
2009-09-30 23:59:59.997 2009-09-30 23:59:59.997
2009-09-30 23:59:59.998 2009-09-30 23:59:59.997
2009-09-30 23:59:59.999 2009-10-01 00:00:00.000

The DATETIME accuracy is 0.00333 seconds. So you need to go to '2009-09-30 23:59:59.998' so it doesn't round up to Oct 1st.

For example:

select '2009-09-30 23:59:59.994', 
  cast('2009-09-30 23:59:59.994' as datetime)
union all select '2009-09-30 23:59:59.995', 
  cast('2009-09-30 23:59:59.995' as datetime)
union all select '2009-09-30 23:59:59.996', 
  cast('2009-09-30 23:59:59.996' as datetime)
union all select '2009-09-30 23:59:59.997', 
  cast('2009-09-30 23:59:59.997' as datetime)
union all select '2009-09-30 23:59:59.998', 
  cast('2009-09-30 23:59:59.998' as datetime)
union all select '2009-09-30 23:59:59.999', 
  cast('2009-09-30 23:59:59.999' as datetime)

returns:

2009-09-30 23:59:59.994 2009-09-30 23:59:59.993
2009-09-30 23:59:59.995 2009-09-30 23:59:59.997
2009-09-30 23:59:59.996 2009-09-30 23:59:59.997
2009-09-30 23:59:59.997 2009-09-30 23:59:59.997
2009-09-30 23:59:59.998 2009-09-30 23:59:59.997
2009-09-30 23:59:59.999 2009-10-01 00:00:00.000

回复收藏 0 原文

~没有更多了~