使用MySQL查找日期范围。

Hi I have following values stored in MySQL table:

你好，我有如下的值存储在MySQL表中:

--- ------------------------     -------------------
Id | StartDate (VARCHAR (20))  | EndDate(VARCHAR(20))
--- ------------------------     -------------------
1  | 03-04-2017                | 18-04-2017

I am using the following SQL to find if the date is within the StartDate and EndDate:

我使用以下SQL来查找日期是否在StartDate和EndDate:

SELECT 
(date_format(str_to_date('03-04-2017','%d-%m-%Y'),'%d-%m-%Y') >= StartDate 
AND 
date_format(str_to_date('03-04-2017','%d-%m-%Y'),'%d-%m-%Y') <= EndDate) AS 
valid 
FROM holiday

My issue is it that when I execute the query and provide 03-04-2017 it returns 1 but also returns 1 when I provide 03-05-2017.

我的问题是，当我执行查询并提供03-04-2017时，它会返回1，但当我提供03-05-2017时也会返回1。

Could someone please highlight what is wrong in this query?

有人能强调一下这个查询中的错误吗?

3 个解决方案

#1

Use a query like this:

使用这样的查询:

SELECT *
FROM holiday
WHERE
    STR_TO_DATE(StartDate,'%d-%m-%Y')
BETWEEN 
    str_to_date('03-04-2017','%d-%m-%Y')
AND
    str_to_date('03-04-2017','%d-%m-%Y');

sample

样本

mysql> SELECT IF(STR_TO_DATE('11-04-2017','%d-%m-%Y')
    ->             BETWEEN
    ->                 str_to_date('03-04-2017','%d-%m-%Y')
    ->             AND
    ->                 str_to_date('10-04-2017','%d-%m-%Y')
    ->
    -> ,'YES','NO') AS ANSWER;
+--------+
| ANSWER |
+--------+
| NO     |
+--------+
1 row in set (0,00 sec)

mysql> SELECT IF(STR_TO_DATE('04-04-2017','%d-%m-%Y')
    ->             BETWEEN
    ->                 str_to_date('03-04-2017','%d-%m-%Y')
    ->             AND
    ->                 str_to_date('10-04-2017','%d-%m-%Y')
    ->
    -> ,'YES','NO') AS ANSWER;
+--------+
| ANSWER |
+--------+
| YES    |
+--------+
1 row in set (0,01 sec)

mysql>

#2

You can use BETWEEN operator to compare the dates, e.g.:

你可以在操作员之间使用来比较日期。

SELECT * 
FROM `table` 
WHERE '2017-04-03' BETWEEN start_date AND end_date;

Update

更新

If the dates are stored as varchar then you need to convert it to date before comparing, e.g.:

如果日期被存储为varchar，那么您需要在比较之前将其转换为日期，例如:

SELECT * 
FROM table 
WHERE '2017-04-03' BETWEEN STR_TO_DATE(start_date, '%d-%m-%Y') AND STR_TO_DATE(end_date, '%d-%m-%Y');

Here's the SQL Fiddle.

这是SQL小提琴。

#3

All other answers are good for your question but in my opinion you should convert your database.

所有其他答案对你的问题都有好处，但我认为你应该转换你的数据库。

It's only sane option.

只有理智的选择。

Using dates in weird VARCHAR format will have big impact in future. Not only it impacts perfomances of your tables right now but you are missing whole MySQL date API ecosystem because of it.

使用怪异的VARCHAR格式的日期将对未来有很大的影响。它不仅影响到您的表的性能，而且还因为它而丢失了整个MySQL日期API生态系统。

Add temporary columns let's say tmp_start_time DATE
添加临时列，比如tmp_start_time日期。
Fill them with dates UPDATE holiday SET tmp_start_time = str_to_date(start_time,'%d-%m-%Y')
填写日期更新日期设置tmp_start_time = str_to_date(start_time，'%d-%m-%Y')
Drop old varchar keys in table
把旧的varchar钥匙放在桌子上。
Add same keys but as DATE
添加相同的键，但是作为日期。
Update them UPDATE holiday SET start_time = tmp_start_time
更新假日设置start_time = tmp_start_time。

From now on you would be able to use BETWEEN as everyone else without str_to_date

从现在开始，您将能够在没有str_to_date的情况下使用其他人。

I just found your comment

我刚找到你的意见。

unfortunately I cannot change schema

不幸的是，我不能改变模式。

ask yourself twice: are you sure?

问自己两次:你确定吗?

#1