MySQL query select all rows that has values inside a date range

Question

I have the following MySQL-query:

SELECT 
  s.student_socialnr, s.student_lastname, s.student_firstname,
  cs.city_name,
  scp.cpl_startdate, scp.cpl_enddate, scp.cpl_invoice,
  cu.customer_name, scp.cpl_coursename
FROM students s
 INNER JOIN studentcourseplan scp ON scp.student_id = s.student_id
 INNER JOIN cityselections cs ON cs.city_id = s.student_city_id
 INNER JOIN customers cu ON cu.customer_id = s.student_customer_id

This will output something like this:

+------------------+------------------+------------------+-----------+---------------+--------------+-------------+---------------+----------------+
| student_socialnr | student_lastname | student_firstname| city_name | cpl_startdate | cpl_enddate  | cpl_invoice | customer_name | cpl_coursename |
+------------------+------------------+------------------+-----------+---------------+--------------+-------------+---------------+----------------+
| 000000-0000      | Doe              | John             | Dallas    | 2012-06-01    | 2012-06-30   | 1337        | The customer  | The course     |
+------------------+------------------+------------------+-----------+---------------+--------------+-------------+---------------+----------------+
| 000000-0000      | Johnsson         | Derp             | Texas     | 2012-02-01    | 2012-07-28   | 5000        | The customer  | The course     |
+------------------+------------------+------------------+-----------+---------------+--------------+-------------+---------------+----------------+
| 000000-0000      | Derpina          | Lisa             | New York  | 2013-01-01    | 2013-04-01   | 2001        | The customer  | The course     |
+------------------+------------------+------------------+-----------+---------------+--------------+-------------+---------------+----------------+

I have t wo questions. I would like to be able to set filters on the fields cpl_startdate and cpl_enddate. So if a user for example sets the filter cpl_startdate = '2012-01-01' and cpl_enddate = '2012-12-31' it should output all rows that has dates between that range. So using the above example it should only output the first two rows, since they are in the selected date-range.

I tried

.. WHERE scp.cpl_startdate <= '2012-01-01' AND scp.cpl_enddate >= '2012-12-31'

.. without luck. Can I use the MySQL syntax BETWEEN somehow?

Also, is it possible to count the days between the two date ranges? I would like to output an INT of the number of days between a span. Not necessarily in the same query..

Zane Bien · Accepted Answer · 2012-06-16 11:04:34Z

2

Switch the comparison signs around:

.. WHERE scp.cpl_startdate >= '2012-01-01' AND scp.cpl_enddate <= '2012-12-31'

To count the number of days between the start and end date of a given row, add this to your select statement:

DATEDIFF(scp.cpl_enddate, scp.cpl_startdate) AS DaysDifference

DATEDIFF(expr1, expr2) returns the date difference of expr1 - expr2. See more in the MySQL documentation.

edited Jun 16, 2012 at 11:04

answered Jun 16, 2012 at 10:57

Zane Bien

23.2k6 gold badges47 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL query select all rows that has values inside a date range

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related