>>> Programming >> MSSQL > How to check if dates are overlapping eachother with SQL? (This page has been seen 1357 times)

How to check if dates are overlapping eachother with SQL?

This can be pretty tricky to do. Lets say you want to make a record in the database with a startdate and a enddate, for a calendar event for example. But you only want to allow it if there is no overlapping dates.

Lets say you already have a record in your database with a StartDate '01/01/2008 10:00:00:000' AND the EndDate is '01/10/2008 10:00:00:000'. Now if a user wants to insert a new record with a StartDate '01/02/2008 10:00:00:000' AND an EndDate '01/09/2008 10:00:00:000' . This would be an overlapping event. You could do a count from the SQL server like this checking before inserting.

SELECT
      Count(*)
FROM
      MyTable
WHERE
      AND DATEDIFF(DAY, 0, MyTable.StartDate) = DATEDIFF(DAY, 0, '01/02/2008 10:00:00:000')       AND NOT ('01/02/2008 10:00:00:000' >= MyTable.EndDate OR '01/09/2008 10:00:00:000' <= MyTable.StartDate)

Like (8)

Dislike (4)

Keywords for this article:
DATEDIFF || DATETIME || SQL || TSQL

Post comment

Name:

Comment:

Code Language:

Code:

Here you can paste a code example. It will then be processed by SyntaxHighlighter and formatted for easier readability.
Please remember to select the correct Code Language in the select above so the SyntaxHighlighter can highlight the code properly.

Code:

Please enter the code you see above

What is 4 + 3 =

Submit Comment

No one has commented on this page yet. You could be the first.

Advertisement by Google