SQL Server 2005根据日期时间主键列检索记录时出错

发布于 2024-09-16 11:59:04 字数 448 浏览 5 评论 0原文

我在 SQL Server 2005 中有一个带有复合主键的表，即外键 (INT) 和日期时间列。

当我运行SQL时，

Select column1 From TABLE where ForeignKey=1 and DateTime='08/26/2010 11:24:36'

如果有这样的记录，则不会返回任何记录。

一种选择是

Select column1 From TABLE where ForeignKey=1 and DateTime>='08/26/2010 11:24:36' and DateTime<'08/26/2010 11:24:37'

但是，似乎...... 我真的必须诉诸这个吗？或者是因为我必须指定直到毫秒？或者有什么解决办法吗？

请帮忙。提前致谢。

原文

I have a table in SQL Server 2005 with composite primary key, i.e. a foreign key (INT) and DateTime column.

When i run the SQL,

Select column1 From TABLE where ForeignKey=1 and DateTime='08/26/2010 11:24:36'

No record was return when there is such a record.

One option to do is

Select column1 From TABLE where ForeignKey=1 and DateTime>='08/26/2010 11:24:36' and DateTime<'08/26/2010 11:24:37'

But then, it just seems...
Do i really have to resort to this? or it is because i have to specify until millisecond?
Or is there any solutions?

Please help. Thanks in advance.

分享到QQ

分享到微博

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

发布评论

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

狠疯拽 2024-09-23 11:59:04

是的，您必须将 DateTime 值的精度与毫秒相匹配才能使相等性生效。

您提出的替代方案将会起作用。作为另一个想法，您可以使用强制转换和转换来截断您的存储的日期时间精确到秒。但请注意，在 where 子句中使用这些函数将否定优化器在日期时间列上使用任何索引的能力。您必须决定这会对您的特定环境产生什么影响。

/* CONVERT style 20 is yyyy-mm-dd hh:mi:ss */
Select column1 
    From TABLE 
    where ForeignKey=1 
        and CAST(CONVERT(char(19),DateTime,20) as datetime) = '08/26/2010 11:24:36'

编辑：根据下面的评论，我注意到 SQL Server 在比较中似乎没有发现 1 毫秒的差异。这在 2000/2005/2008 环境中是正确的。如果我在 2008 年使用新的 datetime2 数据类型，那么它的工作原理如下预期的。阅读 datetime 数据类型，毫秒部分的精度为圆角：

准确度：四舍五入为增量
.000、.003 或 .007 秒

declare @datetime1 datetime
declare @datetime2 datetime
declare @datetime3 datetime
declare @datetime4 datetime

set @datetime1 = '2010-08-30 08:41:51.513'
set @datetime2 = '2010-08-30 08:41:51.513'
set @datetime3 = '2010-08-30 08:41:51.514'
set @datetime4 = '2010-08-30 08:41:51.515'

/* Expected result 'Equal'; Actual result 'Equal' */
if @datetime1 = @datetime2
    print 'Equal'
else
    print 'Not Equal'

/* Expected result 'Not Equal'; Actual result 'Equal' */    
if @datetime1 = @datetime3
    print 'Equal'
else
    print 'Not Equal'

/* Expected result 'Not Equal'; Actual result 'Not Equal' */    
if @datetime1 = @datetime4
    print 'Equal'
else
    print 'Not Equal'

Yes, you would have to match the precision of the DateTime value to the millisecond for the equality to work.

Your proposed alternative will work. As another idea, you could use a cast and convert to truncate your stored datetime down to the second. Note however that using these functions in the where clause will negate the optimizer's ability to use any index on your datetime column. You'll have to decide what impact that would have in your specific environment.

/* CONVERT style 20 is yyyy-mm-dd hh:mi:ss */
Select column1 
    From TABLE 
    where ForeignKey=1 
        and CAST(CONVERT(char(19),DateTime,20) as datetime) = '08/26/2010 11:24:36'

EDIT: Following up on the comments below, I've noticed that SQL Server does not seem to pick up a 1 millisecond difference in a comparison. This is true in 2000/2005/2008 environments. If I use the new datetime2 datatype in 2008, then it works as expected. Reading up on the datetime datatype, the accuracy of the millisecond portion is rounded:

Accuracy: Rounded to increments of
.000, .003, or .007 seconds

declare @datetime1 datetime
declare @datetime2 datetime
declare @datetime3 datetime
declare @datetime4 datetime

set @datetime1 = '2010-08-30 08:41:51.513'
set @datetime2 = '2010-08-30 08:41:51.513'
set @datetime3 = '2010-08-30 08:41:51.514'
set @datetime4 = '2010-08-30 08:41:51.515'

/* Expected result 'Equal'; Actual result 'Equal' */
if @datetime1 = @datetime2
    print 'Equal'
else
    print 'Not Equal'

/* Expected result 'Not Equal'; Actual result 'Equal' */    
if @datetime1 = @datetime3
    print 'Equal'
else
    print 'Not Equal'

/* Expected result 'Not Equal'; Actual result 'Not Equal' */    
if @datetime1 = @datetime4
    print 'Equal'
else
    print 'Not Equal'

回复收藏 0 原文