Solved

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

Posted on 2014-04-08
3
1,297 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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 …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

773 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