I could not think of a good way to phrase this question to search properly if its already been asked.
I'm looking for a way in SQL 2008 R2 to count how many times 6pm occurs between two datetime values.
For example between '2017-04-17 19:00:00' and '2017-04-19 17:00:00' 6pm only occurs once even though the times span 3 different days.
Between '2017-04-17 18:00:00' and '2017-04-19 18:00:00' it occurs 3 times whilst also spanning 3 days.
Heres a really silly made up expression of what I want for illustration.
timecount(hh, 6, min(datefield), max(datefield))
Thank you
A simple query to count:
DECLARE @StartDate datetime = '2017-04-17 18:00:00'
DECLARE @EndDate datetime = '2017-04-19 18:00:00'
SELECT
CASE
WHEN CAST(@StartDate AS time) <= '18:00' AND CAST(@EndDate AS time) >= '18:00'
THEN datediff(day, @StartDate, @EndDate) + 1
WHEN CAST(@StartDate AS time) <= '18:00' AND CAST(@EndDate AS time) < '18:00'
THEN datediff(day, @StartDate, @EndDate)
WHEN CAST(@StartDate AS time) > '18:00' AND CAST(@EndDate AS time) >= '18:00'
THEN datediff(day, @StartDate, @EndDate)
ELSE datediff(day, @StartDate, @EndDate) - 1
END AS TotalCount
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments