I need a SQL query for retrieving data which is included in selected time period, time period is string which user selects from calendar.
For example, if user selects time period '2012-07-01' to '2012-07-04' and I have the following table:
ID || start_date || end_date
1 || 2012-07-02 || 2012-07-04
2 || 2012-07-06 || 2012-08-05
3 || 2012-07-01 || 2012-09-01
4 || 2012-06-25 || 2012-09-01
If a write a sql query like this:
SELECT ID, start_date, end_date
FROM TABLE
WHERE start_date BETWEEN '2012-07-01' AND '2012-07-04'
This returns all rows which started from '2012-07-01' to '2012-07-04'
ID || start_date || end_date
1 || 2012-07-02 || 2012-07-04
3 || 2012-07-01 || 2012-09-01
But, I want all data which is included in this period, so result must be:
ID || start_date || end_date
1 || 2012-07-02 || 2012-07-04
3 || 2012-07-01 || 2012-09-01
4 || 2012-06-25 || 2012-09-01
Row with ID 4 also I want to be in result, because user selected time period is under start_date and end_date.
SELECT ID, start_date, end_date
FROM TABLE
WHERE start_date BETWEEN '2012-07-01' AND '2012-07-04'
You are only selecting by start date, you may ask by:
(start_date between begin, end) OR
(end_date between begin, end) OR
(start_date <= begin AND end_date >= end)
so:
(start_date between begin, end ): you have all events that starts in the range
(end_date between begin, end): you have all events that finish in the range
(start_date <= begin AND end_date >= end): you have all events that are active in the range