Date range overlapping check constraint

by

The CHECK is being executed after the row has been inserted, so the range overlaps with itself.

You’ll need to amend your WHERE to include something like: @MyTableId <> MyTableId.

BTW, your WHERE expression can be simplified.

Ranges don’t overlap if:

  • end of the one range is before the start of the other
  • or start of the one range is after the end of the other.

Which could be written in SQL like:

WHERE @DateEnd < DateStart OR DateEnd < @DateStart

Negate that to get the ranges that do overlap…

WHERE NOT (@DateEnd < DateStart OR DateEnd < @DateStart)

…which according to De Morgan’s laws is the same as…

WHERE NOT (@DateEnd < DateStart) AND NOT (DateEnd < @DateStart)

…which is the same as:

WHERE @DateEnd >= DateStart AND DateEnd >= @DateStart

So your final WHERE should be:

WHERE
    @MyTableId <> MyTableId
    AND @DateEnd >= DateStart
    AND DateEnd >= @DateStart

[SQL Fiddle]

NOTE: to allow ranges to “touch”, use <= in the starting expression, which would produce > in the final expression.

More Related Contents:

  1. What columns generally make good indexes?
  2. How to determine total number of open/active connections in ms sql server 2005
  3. What is the point of “Initial Catalog” in a SQL Server connection string?
  4. Best way to store time (hh:mm) in a database
  5. Database design: Calculating the Account Balance
  6. How to store directory / hierarchy / tree structure in the database?
  7. “Prevent saving changes that require the table to be re-created” negative effects
  8. Difference between database and schema
  9. Updating image in VB and SQL Server not working [closed]
  10. How do I fix the error ‘Named Pipes Provider, error 40 – Could not open a connection to’ SQL Server’?
  11. Storing a file in a database as opposed to the file system?
  12. Preferred Method of Storing Passwords In Database
  13. Why is it considered bad practice to use cursors in SQL Server?
  14. CAST and IsNumeric
  15. When/Why to use Cascading in SQL Server?
  16. SQL server query to get the list of columns in a table along with Data types, NOT NULL, and PRIMARY KEY constraints
  17. SQL Query to find the last day of the month
  18. SQL Server, convert a named instance to default instance?
  19. SQL Server GUID sort algorithm. Why?
  20. WHERE clause on SQL Server “Text” data type
  21. SQL Server error on update command – “A severe error occurred on the current command”
  22. Getting execute permission to xp_cmdshell
  23. SQL server schema and default schema
  24. SQL Server ON DELETE Trigger
  25. “Cannot open user default database. Login failed.” after installing SQL Server Management Studio Express
  26. How to create the linked server for SQL Server 2008 where we have the database from 2000 and 2005
  27. FOR XML PATH(”): Escaping “special” characters
  28. Why is casting from float to varchar being rounded in SQL Server?
  29. Generate table relationship diagram from existing schema (SQL Server) [closed]
  30. What are row, page and table locks? And when they are acquired?

Leave a Comment