With SQL I want to find a date BETWEEN 2 columns with LIKE-CodePudding

So I'm working on a site that uses MySQL and in the reminders table i have 3 columns called date, date_to, and yearly. The dates are stored as for example 2022-06-15.

If for example there is a yearly = 1 reminder with date and date_to being 2021-06-14 and 2021-06-16, how can i look BETWEEN date and date_to if I need to use LIKE because sometimes i need to use the -06-15 part because it's yearly = 1?

I need to use -06-15 sometimes because if its a yearly reminder, i cant just check on the year too because its meant to be repeated every year.

Table example:

| date       | date_to        | yearly |
| -----------| -------------- | ------ |
| 2021-06-14 | 2021-06-16     | 1      |
| 2022-05-03 | 2022-05-04     | 0      |

Expected output after searching for a yearly reminder for date 06-15:

| date       | date_to        | yearly |
| -----------| -------------- | ------ |
| 2021-06-14 | 2021-06-16     | 1      |

CodePudding user response：

If I understand your issue correctly, you just want to check if your date is between a from date and a to_date of your table. This can basically be done with this query:

SELECT date_from, date_to, yearly 
FROM yourtable 
WHERE '2021-06-15' BETWEEN date_from AND date_to;

If you don't care about the year, but want to check the day only, you can use DATE_FORMAT like this:

SELECT date_from, date_to, yearly 
FROM yourtable 
WHERE DATE_FORMAT('2021-06-15', "%m-%d") 
BETWEEN DATE_FORMAT(date_from, "%m-%d") 
AND DATE_FORMAT(date_to, "%m-%d")

Then it doesn't matter which year appears (in this example, 2021), only the day will be checked.

It could also be mentioned that MYSQL also provides a function DAYOFYEAR which will work correctly in most cases for a query like this:

SELECT date_from, date_to, yearly
FROM yourtable
WHERE DAYOFYEAR('2021-06-15') 
BETWEEN DAYOFYEAR(date_from) AND DAYOFYEAR(date_to);

This will also ignore the year and check the day only. It's a bit easier to read, but it's less safe because it could fail when a lag year is involved since then the day of year will be one more than in other years.

Please note I changed the name of "your" column "date" to "date_from" in my answer. This is no mistake, but I recommend to do not use SQL key words or function names as table name or column name. Furthermore, the column name "date_from" better points out the difference to the column "date_to", so you should rename the column if possible.