本文介绍了如何获取TSQL中两个日期之间有多少小时4AM的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我需要获取TSQL中两个日期之间发生的特定小时数.一些例子:

I need to get how many of a specific hour have occurred between two dates in TSQL. Some examples:

以下内容将得出结果= 1

The following would give the result = 1

declare @date1 datetime = '2019-10-01 00:00:00.000';
declare @date2 datetime = '2019-10-02 00:00:00.000';

下面的结果为0,因为它们之间有0个4AM.

The following would give the result = 0, as there has been 0 4AMs in between

declare @date1 datetime = '2019-10-01 05:00:00.000';
declare @date2 datetime = '2019-10-02 00:00:00.000';

以下将给出结果= 2,因为它们之间有2个4AM.

The following would give the result = 2, as there has been 2 4AMs in between

declare @date1 datetime = '2019-10-01 03:00:00.000';
declare @date2 datetime = '2019-10-02 05:00:00.000';

以下内容将给出结果= 2,因为即使它们是在凌晨4:00,也有2个4AM.

The following would give the result = 2, as there has been 2 4AMs even though they are just on the 4:00AM time

declare @date1 datetime = '2019-10-01 04:00:00.000';
declare @date2 datetime = '2019-10-02 04:00:00.000';

我已经尝试过类似的操作...但是它给出了错误的答案

I have tried something like this... but it is giving an incorrect answer

DECLARE @startdate AS DATETIME = '2019-10-01 03:00:00.000'
DECLARE @enddate   AS DATETIME = '2019-10-02 00:00:00.000'
DECLARE @hour int = 4

SELECT DATEDIFF(HOUR, @startdate, @endDate) / 24
     + 1
     + CASE WHEN DATEPART(HOUR, @startdate) <= @hour AND
                 DATEPART(HOUR, @endDate) >= @hour
            THEN 0
            ELSE -1
       END

任何可以透露一些信息的人,我将不胜感激

Anyone who can shed some light I would appreciate it

推荐答案

最简单的方法是从两个值中删除4小时,然后以天为单位求出差值:

The easiest way would be to remove 4 hours from both values, and then get the difference in days:

DECLARE @date1 datetime = '2019-10-01 00:00:00.000';
DECLARE @date2 datetime = '2019-10-02 00:00:00.000';

SELECT DATEDIFF(DAY,DATEADD(HOUR, -4, @date1),DATEADD(HOUR, -4, @date2)); --RETURNS 31, as there are 31 days bewteen 10 Jan and 10 Feb
GO

DECLARE @date1 datetime = '2019-10-01T04:30:00.000';
DECLARE @date2 datetime = '2019-10-02T03:59:00.000';

SELECT DATEDIFF(DAY,DATEADD(HOUR, -4, @date1),DATEADD(HOUR, -4, @date2)); --RETURNS 0, as 04:00 never got to

GO

DECLARE @date1 datetime = '2019-10-01T03:30:00.000';
DECLARE @date2 datetime = '2019-10-03T04:30:00.000';

SELECT DATEDIFF(DAY,DATEADD(HOUR, -4, @date1),DATEADD(HOUR, -4, @date2)); --RETURNS 3, as 04:00 occurs on 01, 02 and 03 of October

似乎OP也希望对 04:00 的事件进行计数.因此,我将开始时间再缩短一秒钟:

Seems the OP wants to count the event of 04:00 as well. I therefore remove a further second from the start time:

DECLARE @date1 datetime = '2019-10-01T04:00:00.000';
DECLARE @date2 datetime = '2019-10-02T04:00:00.000';

SELECT DATEDIFF(DAY,DATEADD(SECOND,-1,DATEADD(HOUR, -4, @date1)),DATEADD(HOUR, -4, @date2)); --returns 2

DECLARE @date1 datetime = '2019-10-01T04:00:00.000';
DECLARE @date2 datetime = '2019-10-01T04:00:01.000';

SELECT DATEDIFF(DAY,DATEADD(SECOND,-1,DATEADD(HOUR, -4, @date1)),DATEADD(HOUR, -4, @date2)); --Returns 1

如果您实际上要存储精确到1/300秒的值,则不要使用1秒,而要使用3毫秒,以确保最大的准确性.

If you're actually storing values accurate to 1/300 of a second, then don't use 1 second, use 3 millseconds, to ensure utmost accuracy.

这篇关于如何获取TSQL中两个日期之间有多少小时4AM的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

07-23 15:31
查看更多