By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,844 Members | 1,859 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,844 IT Pros & Developers. It's quick & easy.

How to do compare ranges of dates in query

P: 1
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
Jan 17 '07 #1
Share this Question
Share on Google+
2 Replies


radcaesar
Expert 100+
P: 759
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
Jan 17 '07 #2

ronverdonk
Expert 2.5K+
P: 4,258
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).
Expand|Select|Wrap|Line Numbers
  1. mysql> SELECT * FROM a WHERE uid=060807 
  2.        AND '2006-01-13'  BETWEEN date_from AND date_to 
  3.        OR  '2006-01-14'  BETWEEN date_from AND date_to;
  4. +----+-------+------------+------------+
  5. | id | uid   | date_from  | date_to    |
  6. +----+-------+------------+------------+
  7. |  1 | 60807 | 2006-01-06 | 2006-01-15 |
  8. +----+-------+------------+------------+
  9. 1 row in set (0.00 sec)
  10. mysql> SELECT * FROM a WHERE uid=060807 
  11.        AND '2006-01-21' BETWEEN date_from AND date_to 
  12.        OR  '2006-01-23' BETWEEN date_from AND date_to;
  13. Empty set (0.00 sec)
  14. mysql>
So when something is returned, the dates are taken.
When an empty result is returned you can book the user.

Ronald :cool:
Jan 17 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.