title: Date range overlapping check constraint
body: <p>I've a simple table in sql server 2005 with 3 columns: DateStart, DateEnd and Value. I tried to set a "table check constraint" to avoid inserting overlapping records. For instance if in such table there is a record with DateStart = 2012-01-01 (first January) and DateEnd 2012-01-15 (15th January) than Check constraint must avoid inserting a record with DateStart=2012-01-10 (no care DateEnd), a record with DateEnd=2012-01-10 (no care DateStart) or a record with DateStart 2011-12-10 and DateEnd 2012-02-01.</p>
  
  <p>I defined a UDF in such way:</p>
  
  <pre><code>CREATE FUNCTION [dbo].[ufn_checkOverlappingDateRange]
  (
      @DateStart AS DATETIME
      ,@DateEnd AS DATETIME
  )
  RETURNS BIT 
  AS
  BEGIN
    DECLARE @retval BIT
    /* date range at least one day */
    IF (DATEDIFF(day,@DateStart,@DateEnd) &lt; 1)
      BEGIN
        SET @retval=0
      END
    ELSE
      BEGIN
        IF EXISTS
          (
            SELECT
                *
              FROM [dbo].[myTable]
              WHERE
              ((DateStart &lt;= @DateStart) AND (DateEnd &gt; @DateStart))
              OR
              ((@DateStart &lt;= DateStart) AND (@DateEnd &gt; DateStart))
          )
          BEGIN
            SET @retval=0
          END
      ELSE
        BEGIN
              SET @retval=1
            END
          END
    RETURN @retval
  END
  </code></pre>
  
  <p>Then thought check could be this:</p>
  
  <pre><code>ALTER TABLE [dbo].[myTable]  WITH CHECK ADD  CONSTRAINT [CK_OverlappingDateRange] CHECK  ([dbo].[ufn_checkOverlappingDateRange]([DateStart],[DateEnd])&lt;&gt;(0))
  </code></pre>
  
  <p>But even with [myTable] empty EXISTS Operator returns true when i insert first record. Where i'm wrog ? Is it possible to set a constraint like this ?</p>
  
  <p>BTW I consider  DateStart includes in range and DateEnd excludes from range.</p>