MySQL Is A Date Between Date Range

user_10137328 0
Viewed 83

I have a table which contains start and end dates of rooms. I wrote this SQL:

SELECT ic.invoice_id,
       pccr.room_name,
       DATEDIFF(end, start) AS 'Total Days',
       pccr.start,
       pccr.end,
FROM partners_campuses_courses_rooms AS pccr
         JOIN invoices_courses AS ic ON pccr.course_id = ic.course_id
         JOIN invoices i on ic.invoice_id = i.invoice_id
WHERE pccr.deleted_at IS NULL
    AND pccr.end BETWEEN '2022-12-01' AND '2022-12-31'

sql result

The rooms are active between these dates. But, when I filter like this,

SELECT ic.invoice_id,
       pccr.room_name,
       DATEDIFF(end, start) AS 'Total Days',
       pccr.start,
       pccr.end,
FROM partners_campuses_courses_rooms AS pccr
         JOIN invoices_courses AS ic ON pccr.course_id = ic.course_id
         JOIN invoices i on ic.invoice_id = i.invoice_id
WHERE pccr.deleted_at IS NULL
    AND pccr.end BETWEEN '2022-12-01' AND '2022-12-30'

I have no results. These dates are like an array:

dates = ['2017-01-01', '2017-01-02', '2017-01-03', ... , '2022-12-30', '2022-12-31']

So, you can see that room is active on 2022-12-30 but I can't get it. How can I do that?

Hope that I express myself well.

mysql sql
4 Answers

If you want rows that are active on a given date, use this logic:

WHERE pccr.deleted_at IS NULL AND
      pccr.end >= $date AND
      pccr.start <= $date

If you have a range of dates, then use:

WHERE pccr.deleted_at IS NULL AND
      pccr.end >= $start_date AND
      pccr.start <= $end_date
user_11440350

You need to select room with start date before end of period and start date after start of period

SELECT ic.invoice_id,
       pccr.room_name,
       DATEDIFF(end, start) AS 'Total Days',
       pccr.start,
       pccr.end,
FROM partners_campuses_courses_rooms AS pccr
         JOIN invoices_courses AS ic ON pccr.course_id = ic.course_id
         JOIN invoices i on ic.invoice_id = i.invoice_id
WHERE pccr.deleted_at IS NULL
    AND pccr.start < '2022-12-30' AND pccr.end > '2022-12-01'
user_37000280

By doing

WHERE pccr.deleted_at IS NULL
AND pccr.end BETWEEN '2022-12-01' AND '2022-12-30'

You're checking if any of the end dates are between '2022-12-01' AND '2022-12-30'. And none of the end dates are. So it makes sense that you get no results.

Are you trying to see which rooms are active in a specific date range? If so, what uou want to do, is check if the pccr.end is >= your startdate, and check if pccr.start is smaller than your end date. This way you are getting everything between or on those dates.

So with your example:

SELECT ic.invoice_id,
       pccr.room_name,
       DATEDIFF(end, start) AS 'Total Days',
       pccr.start,
       pccr.end,
FROM partners_campuses_courses_rooms AS pccr
         JOIN invoices_courses AS ic ON pccr.course_id = ic.course_id
         JOIN invoices i on ic.invoice_id = i.invoice_id
WHERE pccr.deleted_at IS NULL
    AND pccr.end >= '2022-12-01' 
    AND pccr.start <= '2022-12-30'
user_127129620

I guess the problem is that the second date is exclusive. It is like saying end_date IS LESS THAN '2022-12-30'. That means it will include everything until '2022-12-29 23:59:59'.

So, you should add one more day. Your query should look like:

WHERE pccr.deleted_at IS NULL
    AND pccr.end BETWEEN '2022-12-01' AND '2022-12-31'
user_154314880
Related