CODEWITHSUNDEEP
sqlsql-servert-sqldatetime
user3284707
user3284707

Reputation: 3351

How to get how many hour 4AM have there been between two dates in TSQL

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

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';

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';

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';

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

Upvotes: 1

Views: 125

Answers (4)

Salman Arshad
Salman Arshad

Reputation: 272406

If two dates are n hours apart then there are FLOOR(n / 24) 4AMs between them; plus an additional one if 4AM lies between hour of start and end date (checking if 04:00 is between 03:00 - 05:00 is easy, checking if 04:00 is between 05:00 - 03:00 is hard):

SELECT date1
     , date2
     , DATEDIFF(HOUR, date1, date2) / 24 + CASE
           WHEN H1 <= H2 AND (H1 <= 4 AND 4 <= H2) OR H1 >  H2 AND (H1 <= 4 OR  4 <= H2) THEN 1
           ELSE 0
       END AS result
FROM tests
CROSS APPLY (SELECT DATEPART(HOUR, date1), DATEPART(HOUR, date2)) AS x(h1, h2)

Demo on db<>fiddle

Upvotes: 1

VBAGuy
VBAGuy

Reputation: 172

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

DECLARE @Hr INT=4
DECLARE @AMorPM CHAR(2)='PM'

;WITH CTE AS
(
    SELECT @date1 AS DateStart
    UNION ALL
    SELECT DATEADD(HOUR,1,DateStart)
    FROM CTE 
    WHERE DateStart < @date2
)
SELECT COUNT(CASE WHEN DateStart > 12 THEN DateStart-12 ELSE DateStart END) AS TotalTimes
FROM CTE
WHERE (
        (@AMorPM='AM' AND DATEPART(HOUR,DateStart)=@Hr) OR 
        (@AMorPM='PM' AND DATEPART(HOUR,DateStart)-12=@Hr)
      )

Upvotes: 0

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131729

This sounds like an attempt to count days in one timezone when the values are stored in another, probably UTC.

One option is to use TODATETIMEOFFSETto convert the UTC date to the target timezone : 

SELECT datediff(d,todatetimeoffset(@date1,'+04:00'), todatetimeoffset(@date2,'+04:00'))

This makes the timezone calculation explicit. It won't handle daylight changes though as the offset has to be specified explicitly. Should the DST rules change, the application itself will have to be modified to change the offsets.

In SQL Server 2016 and later, AT TIME ZONE allows specifying the timezone name directly and applies any timezone rules specified for that timezone. 

select 
    datediff(d, @date1 at time zone 'Caucasus Standard Time',
                @date2 at time zone 'Caucasus Standard Time')

Timezone rules are distributed through Windows Updates, which means rule changes no longer require modifications to the application or the database.

Upvotes: -1

Thom A
Thom A

Reputation: 96028

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

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

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.

Upvotes: 5

Related Questions