Link to home
Create AccountLog in
Avatar of tonyhhisc
tonyhhisc

asked on

Running a Query Between Dates

Please help me figure out how to do this, how can a query be written for this? Or is there some php needed beforehand, I'm lost!


I have a Users table with their program start and end dates:


Name       Start           End
-----------------------------------------
Tony       Jan 1, 2012     Feb 1, 2012
Kamal      Jan 1, 2012     Mar 1, 2012
Shailesh   Feb 29, 2012    Oct 14, 2012    


I want to do a search for users that are in the program between the dates of.....(examples)...

Range: Jan 2, 2012 to March 5, 2012
Expected Result:  Tony, Kamal, Shailesh

Range: Feb 2, 2012 to Feb 20, 2012
Result: Kamal

Range: Jan 9, 2012 to Feb 28, 2012
Result: Tony, Kamal


Since the days between the start and end dates are not stored in the database, how in the ***** do I run a query on them?
I guess I could just store all the days between the dates in the database, but that seems like a bad solution.

Thanks for your help!

Tony
Avatar of ralmada
ralmada
Flag of Canada image

something like this


select * from Users
where Start >= '2012-01-02' and End <= '2012-03-05'
Avatar of lludden
Create a calendar table.  Here is a good article describing them: http://weblogs.sqlteam.com/dang/archive/2010/07/19/calendar-table-and-datetime-functions.aspx

Once you have it, all sorts of queries are easier, including yours:

SELECT DISTINCT myTable.* FROM myTable INNER JOIN calendar ON CalendarTable.Date BETWEEN myTable.StartDate AND MyTable.EndDate
WHERE CalendarTable.Date BETWEEN @StartDate AND @EndDate

ASKER CERTIFIED SOLUTION
Avatar of Scott Madeira
Scott Madeira
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Where "Start" and "End" are your column names and StartDate and EndDate are the beginning and end of the date ranges, respectively:
SELECT whatever WHERE (Start BETWEEN StartDate AND EndDate) OR (End BETWEEN StartDate AND EndDate)
Actually

select * from Users
where Start between '2012-01-02' and '2012-03-05'
Typo...  I meant:

In your where clause you want all rows where (UserStartDate < RangeEndDate) and (UserEndDate > RangeStartDate)
actually

select * from Users
where Start < '2015-03-05' and End > '2012-01-02'
Avatar of tonyhhisc
tonyhhisc

ASKER

ralmada:

If I searched for Dates:

Jan 4th 2012
to
Jan 8th 2012

It should Display first two users (Tony, Kamal)... since they were in the program in that date range.

However with your solution wouldn't it NOT show them, because the start date listed in the database (Jan 1, 2012) will not "fall between" the dates above.

See the issue here? Thanks a lot for your help!
lludden:seems a bit overcomplicated... is there not an easier way?
>>See the issue here? Thanks a lot for your help!<<

Have you checked my last comment http:#a37102532  I messed up with the dates in my first reply

so if you want to search for Jan 4th 2012 to  Jan 8th 2012

select * from Users
where Start < '2012-01-08' and End > '2012-01-04'

should bring tony and kamal
Try something like this


Given:
$search_start = '2012-01-04';
$search_end  = '2012-01-08';

$sql_command = "select * from users where (user.start between '$search_start' and '$search_end') or (user.end between '$search_start' and '$search_end')";
^  I already posted that solution.  No response from the OP yet.
Idunno... i have to ask, are you storing dates in mysql format or as in your example (Jan 1, 2012 )
Oops, sorry paulmacd. You win. :)
Please read this article.  It shows some of the ways you can handle DATETIME information in PHP and MySQL.
https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html

I do not understand this part: Since the days between the start and end dates are not stored in the database...  Is this an HTML table or is it a SQL table?

Make sure you are using the ISO-8601 representation of dates for all internal work.  You only want to format "pretty" dates when you send the output to the browser.
This is your solution


If I searched for Dates:

Jan 4th 2012
to
Jan 8th 2012

It should Display first two users (Tony, Kamal)... since they were in the program in that date range.

SELECT * FROM Users WHERE StartDate<='2012-01-04' AND EndDate>='2012-01-12'

I have tested it.
What you mean by users that are in the program?

here in your first example

Name       Start           End
-----------------------------------------
Tony       Jan 1, 2012     Feb 1, 2012
Kamal      Jan 1, 2012     Mar 1, 2012
Shailesh   Feb 29, 2012    Oct 14, 2012  

Range: Jan 2, 2012 to March 5, 2012
Expected Result:  Tony, Kamal, Shailesh

based on what you will get this can you explain in words your result?

what you mean by Range Range of what? here you have 2 users with start date less than the Range Jan 2 but non has end date greater or equal to March 5 of this 2

A calendar table would really make things a lot easier.  Make one, and you can find all sorts of uses for it.
Ray_Paseur: Sorry for the confusion, yes the data is a SQL table.
I meant, since I can't search for all the "in-between" days in the database (that data is not there), how can i query for that?

And "the program" basically means the database. The table basically represents a user's list for a program, the start and end date represents when the program starts and ends for each user.

If I have a user who's start and end dates are (same year):
Start: Jan 1st
End:  Feb 1st

And I do a search between dates:
Jan 3rd  -to- Jan 5th

That user should show up, because Jan 3rd and Jan 5th are both dates found BETWEEN the searched dates.

This is so hard to explain on text, thanks for all your patience and assistance.


"That user should show up, because Jan 3rd and Jan 5th are both dates found BETWEEN the searched dates."

CORRECTION

"That user should show up, because Jan 3rd and Jan 5th are both dates found BETWEEN the user's dates."
paulmacd,

Your Solution will only search the start and end dates and will ignore all the dates between them, but thank you for your time and assistance.
You just made my day!!! Thank you!!
I knew it was much simpler than I was making it. I was just getting confused, haha!