问题描述
我需要获取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的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!