Solved

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

Posted on 2014-04-08
3
1,312 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
updating the date data 12 40
Create a MySQL table as easily as possible 6 61
Problem with MySQL query - graph 3 26
Dynamic Table mySQL stored procedure 5 37
All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

733 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