Solved

MySQL Query two date values, determine if between submitted date range

Posted on 2014-04-08
3
1,274 Views
Last Modified: 2014-04-22
I have two date fields in a table, checkInDate and checkOutDate.  I have a web form that sends a startdate and enddate.  I need to build a query (or sp?) that will do a couple things:

- pull records from my Totes table if the range between totes.checkInDate and totes.checkOutDate somehow intercepts the range between form.startdate and form.enddate;

- calculate the number of days in the range between totes.checkInDate and totes.checkOutDate fall into the range between form.startdate and form.enddate

The Totes table is very simple:

binID, checkInDate, checkOutDate, customerID


Any help is greatly appreciated.
0
Comment
Question by:Carlos Elgueta
3 Comments
 
LVL 6

Assisted Solution

by:Dulton
Dulton earned 150 total points
ID: 39986611
The where clause should get your the intercepting dates. I'm not sure on the MySQL syntax of the datediff.
select *, datediff(checkOutDate,checkInDate) AS [DaysInRange] 
from totes where totes.checkOutDate >= form.startdate OR totes.checkInDate <= form.enddate

Open in new window

0
 
LVL 5

Accepted Solution

by:
Jalpa Kotak earned 350 total points
ID: 39986881
select *, datediff(dd,checkInDate,checkOutDate) AS [DaysInRange]
from totes where totes.checkOutDate >= form.startdate AND totes.checkInDate <= form.enddate AND totes.checkOutDate <=form.enddate AND totes.checkInDate>=form.startdate
0
 
LVL 1

Author Closing Comment

by:Carlos Elgueta
ID: 40015860
Split points. @Dulton was first, @jalpa_144 had it closer to what I needed.  Thank you both.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
A lot of articles have been written on splitting mysqldump and grabbing the required tables. A long while back, when Shlomi (http://code.openark.org/blog/mysql/on-restoring-a-single-table-from-mysqldump) had suggested a “sed” way, I actually shell …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now