Find conflicts between date and time using SQL server 2012

Question

I've got this table here with startDate, endDate, startTime and endTime column.

startDate   endDate     startTime  endTime
2020-01-01  2020-01-02  13:00:00   11:00:00
2020-01-14  2020-01-14  08:00:00   16:00:00
2020-02-01  2020-03-01  12:00:00   12:00:00

I've also got this new set of data:

startDate   endDate     startTime  endTime
2020-01-12  2020-01-15  13:00:00   11:00:00
2020-01-29  2020-02-10  08:00:00   16:00:00

How can I check if the new set of data will have any conflicts with the dates and times which are already in the table? And can I also get a list of dates and times from the table?

Update - What I've already tried is to combine startDate and startTime into a new column as startDateTime where it'll hold datetime datatype so I can just use in between statement to check it but I can't seem to combine those two columns together.

You mean, if the datetime ranges are [overlapping](https://stackoverflow.com/tags/overlap/info)? — Zohar Peled, Sep 05 '19 at 09:19
*"And can I also get a list of dates and times from the table?"* Yes, by a `SELECT`? — Thom A, Sep 05 '19 at 09:20
By that I meant I need fields which are overlapping with the new data — hussainsajid, Sep 05 '19 at 09:25
Possible duplicate of [How to select overlapping date ranges in SQL](https://stackoverflow.com/questions/49836518/how-to-select-overlapping-date-ranges-in-sql) — Amira Bedhiafi, Sep 05 '19 at 09:26

mkRabbani · Accepted Answer · 2019-09-05T09:51:09.593

Try this option using join. This will return only conflicting rows from the new data set.

WITH your_table(startDate,endDate,startTime,endTime)
AS
(
    SELECT '2020-01-01','2020-01-02','13:00:00','11:00:00' UNION ALL
    SELECT '2020-01-14','2020-01-14','08:00:00','16:00:00' UNION ALL
    SELECT '2020-02-01','2020-03-01','12:00:00','12:00:00'
),
new_data_set(startDate,endDate,startTime,endTime)
AS
(
    SELECT '2020-01-12','2020-01-15','13:00:00','11:00:00' UNION ALL
    SELECT '2020-01-29','2020-02-10','08:00:00','16:00:00'
)

SELECT A.*
FROM new_data_set A
INNER JOIN your_table B 
ON  CAST(A.startDate+' '+A.startTime AS DATETIME)
    BETWEEN CAST(B.startDate+' '+B.startTime AS DATETIME)
    AND CAST(B.endDate+' '+B.endTime AS DATETIME)

    OR  

    CAST(A.endDate+' '+A.endTime AS DATETIME)
    BETWEEN CAST(B.startDate+' '+B.startTime AS DATETIME)
    AND CAST(B.endDate+' '+B.endTime AS DATETIME)


    OR 
    (
        CAST(A.startDate+' '+A.startTime AS DATETIME) <  CAST(B.startDate+' '+B.startTime AS DATETIME)
        AND
        CAST(A.endDate+' '+A.endTime AS DATETIME) >   CAST(B.endDate+' '+B.endTime AS DATETIME)
    )

Output is-

startDate   endDate     startTime   endTime
2020-01-12  2020-01-15  13:00:00    11:00:00
2020-01-29  2020-02-10  08:00:00    16:00:00

Thanks but the output should be both rows since they both are conflicting. — hussainsajid, Sep 05 '19 at 09:46

Find conflicts between date and time using SQL server 2012

1 Answers1