SQL Server 中的 DateDiff 舍入

发布于 2024-10-06 11:43:48 字数 373 浏览 0 评论 0原文

我有一个表，其中包含运行特定函数的最后日期/时间。在我的存储过程中，如果当前日期/时间距上次运行时间超过一个小时，我想返回 1；如果小于，则返回 0。目前我有这个：

IF((SELECT TOP 1 DATEDIFF(HH,LastRunDateTime,GETDATE()) FROM myTable) >= 1)
BEGIN
    -- run statement to update LastRunDateTime
        return 1;
END
ELSE
    return 0;
END

How does round work with DateDiff?

有没有更好的方法，如果已经超过一个小时，则只返回 1 ？

原文

I have a table that contains the last date/time a particular function was ran. In my stored procedure, I want to return 1 if the current date/time is more than an hour away from the last time it was ran and 0 if it was less. Currently I have this:

IF((SELECT TOP 1 DATEDIFF(HH,LastRunDateTime,GETDATE()) FROM myTable) >= 1)
BEGIN
    -- run statement to update LastRunDateTime
        return 1;
END
ELSE
    return 0;
END

How does rounding work with DateDiff?

Is there a better way to only return 1 if it has been more than an hour?

分享到QQ

分享到微博

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

发布评论

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

甜警司 2024-10-13 11:43:48

使用：

SELECT CASE 
         WHEN DATEDIFF(hh, LastRunDateTime, GETDATE()) >= 1 THEN 1 
         ELSE 0 
       END

参考：

DATEDIFF

舍入如何与 DateDiff 一起使用？

提供的文档链接中的日期部分边界部分列出了此类信息。

Use:

SELECT CASE 
         WHEN DATEDIFF(hh, LastRunDateTime, GETDATE()) >= 1 THEN 1 
         ELSE 0 
       END

Reference:

DATEDIFF

How does rounding work with DateDiff?

The datepart Boundaries section in the documentation link provided lists such information.

回复收藏 0 原文

九命猫 2024-10-13 11:43:48

我认为您希望

IF DATEADD(hour, LastRunDateTime, 1) <= GETDATE() BEGIN
    RETURN 1;
END;
ELSE BEGIN
    RETURN 0;
END;

DateDiff 比仅仅减去两个日期时间更微妙。它实际上告诉你两者之间跨越了多少“界限”。例如：

PRINT DATEDIFF(HH, '2010-12-07T03:59:59', '2010-12-07T04:00:00');
PRINT DATEDIFF(HH, '2010-12-07T04:00:00', '2010-12-07T04:59:59');

打印以下内容：

1
0

这很令人困惑，因为第二对日期时间相距较远。当您需要时，此功能非常有用，但当您不需要时，它就违反直觉。

I think you want

IF DATEADD(hour, LastRunDateTime, 1) <= GETDATE() BEGIN
    RETURN 1;
END;
ELSE BEGIN
    RETURN 0;
END;

DateDiff is a little more subtle than just subtracting two datetimes. It actually tells you how many "boundaries" are crossed between the two. For example:

PRINT DATEDIFF(HH, '2010-12-07T03:59:59', '2010-12-07T04:00:00');
PRINT DATEDIFF(HH, '2010-12-07T04:00:00', '2010-12-07T04:59:59');

prints the following:

1
0

which is confusing, because the second pair of datetimes are farther apart. This functionality is great when you need it, but counterintuitive when you don't.

回复收藏 0 原文

~没有更多了~