[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2014-04-08
3
Medium Priority
?
1,358 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 600 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 1400 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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

649 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