Search code examples
mysqldate-range

Query to select date range from different columns

I have a from_date and to_date columns in my LeaveApplication table. So I'm trying to check if the specified date is in range of the two columns. For example:

from_date : 2021-08-26
to_date : 2021-08-29
date : 2021-08-27

I was trying to use the BETWEEN operator but the syntax is to select the date range from the same column name.

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

In my case, I want to select the date range from different columns something like the query example below. Is there a way to achieve this?

SELECT * FROM LeaveApplication
WHERE "2021-08-27" BETWEEN (from_date = "2021-08-26") AND (to_date = "2021-08-29")

Thank you!

Solution

  • Using greater, equal, and less operator didn't work for me but actually it can be done with BETWEEN operator this way

    SELECT * FROM LeaveApplication WHERE "2021-08-27" BETWEEN from_date AND to_date

    Found this example here

    p/s: my attempt in my example is quite close to it :)