Arash Ghasemi Rad Arash Ghasemi Rad - 5 months ago 8
SQL Question

find time conflicts between two dates

I am working on a query that should find lunch times between two dates.
for example, lunch time is between 14:00 PM and 15:30 PM.
and dates are: '2016-06-09 10:00:00.000' and '2016-06-11 10:00:00.000'
lunch time accrues two times between these dates.
and another examples:


'2016-06-09 15:00:00.000' and '2016-06-11 10:00:00.000' : 2 Times
'2016-06-09 17:00:00.000' and '2016-06-11 10:00:00.000' : 1 Time
'2016-06-09 13:00:00.000' and '2016-06-11 15:00:00.000' : 3 Times


but I can never do it :(

Answer

Try this and make tweeks according to your requirements:

DECLARE @date1 DATETIME = '2016-06-09 08:30:00.000';
DECLARE @date2 DATETIME = '2016-06-13 18:00:00.000';
DECLARE @lunchStart DATETIME = '2016-06-13 14:00:00.000';
DECLARE @lunchEnd DATETIME = '2016-06-13 15:30:00.000';
DECLARE @output INT = 0;

SELECT @output = DATEDIFF(DAY, @date1, @date2)
IF DATEPART(HOUR, @date1) > DATEPART(HOUR, @lunchStart) OR (DATEPART(HOUR, @date1) = DATEPART(HOUR, @lunchStart) AND DATEPART(MINUTE, @date1) <= DATEPART(MINUTE, @lunchStart))
    SET @output = @output - 1

IF DATEPART(HOUR, @date2) < DATEPART(HOUR, @lunchEnd) OR (DATEPART(HOUR, @date2) = DATEPART(HOUR, @lunchEnd) AND DATEPART(MINUTE, @date2) = DATEPART(MINUTE, @lunchEnd))
    SET @output = @output - 1

PRINT @output