Solved

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

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
join tables 4 56
Excel - SQL export question 3 49
MySQL Memory Keeps Increasing 4 47
two ways encryption with php 3 37
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…
This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

837 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