Date range overlapping check constraint

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.