CODEWITHSUNDEEP
phpmysqldate
Michael Smith
Michael Smith

Reputation: 665

SQL for locating a date range using a date range

Ok, so this is a little confusing (hence why I am asking for help)

I have a table in a MySQL database that looks like so:

+-----+-------------+------------+------------+---------------+---------+-----------+---------+
| id  | supplier_id | start_date | end_date   | days_attended | user_id | client_id | trashed |
+-----+-------------+------------+------------+---------------+---------+-----------+---------+
|   1 |          15 | 1410098400 | 1410703200 |             2 |       7 |       424 |       0 |
|   2 |          84 | 1411912800 | 1420117200 |             3 |       7 |       395 |       0 |
|   3 |         183 | 1390827600 | 1418907600 |             2 |       7 |        96 |       0 |
|   4 |          85 | 1412431200 | 1419512400 |             5 |       7 |       105 |       0 |
|   5 |         168 | 1411912800 | 1412258400 |             3 |       7 |        53 |       0 |

My dilemma is that I want to create a report from this table that takes a date range as input and the returns a list of dates from within that date range.

As you can see there is a start and end date for each row. So I need a SQL query that will search for a date within these two dates PER ROW using the two dates selected by the user.

i.e. User selects date range: 21/09/2015 to 21/12/2015. Then the query will look for dates between these dates by using the existing date range in the table.

Upvotes: 0

Views: 85

Answers (2)

dhi_m
dhi_m

Reputation: 1265

Please try this

SELECT * FROM table WHERE (
start_date >= UNIX_TIMESTAMP(STR_TO_DATE(var_date_1, '%d/%M/%Y')) AND 
end_date <= UNIX_TIMESTAMP(STR_TO_DATE(var_date_2, '%d/%M/%Y'))
) ;

Upvotes: 1

curtwphillips
curtwphillips

Reputation: 5811

If you want to check if either the start_date or end_date is between the user's start date and end date.

You could plug the users values in for the user_start_date and user_end_date here:

SELECT * FROM table 
WHERE (start_date >= user_start_date AND start_date <= user_end_date) 
OR (end_date >= user_start_date AND end_date <= user_end_date);

Upvotes: 2

Related Questions