How to insert a record with date validation

Question

Table1

ID  Month   datefrom   dateto     reason

001 12/2011 01/12/2011 10/12/2011 Leave
001 12/2011 18/12/2011 25/12/2011 Holiday
.....

datefrom, dateto datatype is datetime, datefrom & dateto format is (dd/mm/yyyy)

Before inserting datefrom,dateto i want to check whether it is matching with datefrom and dateto from table

Tried Query (Before Inserting i am checking whether user entry date is matching with table)

SELECT * FROM table1 
WHERE (Month = '01/2012') AND 
      (id = '001') and 
      Convert(Datetime, '01/12/2011', 103)  between datefrom and dateto or 
      Convert(Datetime, '10/12/2011', 103)  between datefrom and dateto

The above query is working if i select the date from 01/12/2011 to 10/12/2011, incase if i selecting the date from 31/11/2011 to 11/12/2011 means it is allowing to insert...

How to modify my query....

Need query Help

Do you want to select records where any part of the record's date range is within the specified date range, where the whole of the record's date range is within the specified date range, or something else? — , Jan 04 '12 at 12:43

score 1 · Accepted Answer · answered Jan 04 '12 at 12:54

This is a very (, very) wild guess. Are you are trying to INSERT into the table but you don't want the [datefrom, dateto] intervals to cross with any existing interval in the table?

If yes, you could do this:

INSERT INTO table1
  (datefrom, dateto)
SELECT @DateFromNewValue, @DateToNewValue
WHERE NOT EXISTS
      ( SELECT *
        FROM table1
        WHERE datefrom <= @DateToNewValue
          AND @DateFromNewValue <= dateto
      )

and have any @DateNewValue in 'yyyy-mm-dd' format.

How to insert a record with date validation

1 Answers1