469,331 Members | 1,749 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,331 developers. It's quick & easy.

How to do compare ranges of dates in query

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
2 2605
radcaesar
759 Expert 512MB
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
4,258 Expert 4TB
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.

Similar topics

9 posts views Thread by Thomas R. Hummel | last post: by
12 posts views Thread by Steve Elliott | last post: by
67 posts views Thread by PC Datasheet | last post: by
1 post views Thread by Matt | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by Purva khokhar | last post: by
1 post views Thread by haryvincent176 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.