How to do compare ranges of dates in query

[joeybarbecho]

hello...
I have a program that saves dates.

Problem:
Given:

sample table: Leave
------------------------------------------
ID | UserID | DateFrom | DateTo|
----------------------------------------------------
1 | 060807| 2006-01-06 | 2006-01-15
2 | 060807| 2006-01-16 | 2006-01-20
----------------------------------------------------

Input from the user:

Example A:
ID : 3
UserID : 060807
DateFrom: 2006-01-13
DateTo: 2006-01-14

Query Result: Cannot add: Range of dates already exist.

Example B:
ID : 3
UserID : 060807
DateFrom: 2006-01-21
DateTo: 2006-01-23

Query Result: Data added succesfully.


Question:
what mysql query can I use to check the range of the DateFrom and DateTo from the user and compare it to the table and check whether it would conflict to the existing ranges of dates?

Thank you very much and I hope anyone can help me....huhuhu

[radcaesar]

Use datediff() method

any problems, refer Date and Time functions
:)

hello...
I have a program that saves dates.

Problem:
Given:

sample table: Leave
------------------------------------------
ID | UserID | DateFrom | DateTo|
----------------------------------------------------
1 | 060807| 2006-01-06 | 2006-01-15
2 | 060807| 2006-01-16 | 2006-01-20
----------------------------------------------------

Input from the user:

Example A:
ID : 3
UserID : 060807
DateFrom: 2006-01-13
DateTo: 2006-01-14

Query Result: Cannot add: Range of dates already exist.

Example B:
ID : 3
UserID : 060807
DateFrom: 2006-01-21
DateTo: 2006-01-23

Query Result: Data added succesfully.


Question:
what mysql query can I use to check the range of the DateFrom and DateTo from the user and compare it to the table and check whether it would conflict to the existing ranges of dates?

Thank you very much and I hope anyone can help me....huhuhu

[ronverdonk]

Given: when the user-requested start date or end date falls within the date ranges in any of the user's records, it means that any or both of these dates are reserved already.

See the following statements that do this for your samples (I use your thread's dates to make it more visible).

mysql> SELECT * FROM a WHERE uid=060807 
       AND '2006-01-13'  BETWEEN date_from AND date_to 
       OR  '2006-01-14'  BETWEEN date_from AND date_to;
+----+-------+------------+------------+
| id | uid   | date_from  | date_to    |
+----+-------+------------+------------+
|  1 | 60807 | 2006-01-06 | 2006-01-15 |
+----+-------+------------+------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM a WHERE uid=060807 
       AND '2006-01-21' BETWEEN date_from AND date_to 
       OR  '2006-01-23' BETWEEN date_from AND date_to;
Empty set (0.00 sec)
mysql>

So when something is returned, the dates are taken.
When an empty result is returned you can book the user.

Ronald :cool: