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
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
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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)
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'
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)
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'
select * from Users
where Start < '2015-03-05' and End > '2012-01-02'
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!
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!
ASKER
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
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
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')";
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.
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.
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
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.
ASKER
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.
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.
ASKER
"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."
CORRECTION
"That user should show up, because Jan 3rd and Jan 5th are both dates found BETWEEN the user's dates."
ASKER
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.
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.
ASKER
You just made my day!!! Thank you!!
I knew it was much simpler than I was making it. I was just getting confused, haha!
I knew it was much simpler than I was making it. I was just getting confused, haha!
select * from Users
where Start >= '2012-01-02' and End <= '2012-03-05'