dongtiran7769
dongtiran7769
2015-03-22 14:01

从mysql查询中提取特定日期

已采纳

I have to columns in my table (check_in and checkout). Both of them has this format dd-mm-yyyy. I want to check if there is any date available between my two paramaters(inputs).

I tried like this:

select * from rezervare where date(check_out) > date('my_checkout_input') 

but this works only in a few cases. If i have a reservation between 12-03-2015 - 15-03-2015 and another reservation between 17-03-2015 and 20-03-2015. It will not take the date between 16-03-2015 as a free date. Can you give me some ideas ?

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

3条回答

  • dpwjx32578146 dpwjx32578146 6年前
    create table rezervare (
        id int auto_increment,
        check_in varchar(10),
        checkout varchar(10),
        primary key (id)
    );
    insert into rezervare values
        (null, '12-03-2015', '12-03-2015'),
        (null, '13-03-2015', '13-03-2015'),
        (null, '14-03-2015', '14-03-2015'),
        (null, '15-03-2015', '15-03-2015'),
        (null, '16-03-2015', '16-03-2015'),
        (null, '17-03-2015', '17-03-2015'),
        (null, '19-03-2015', '19-03-2015'),
        (null, '20-03-2015', '20-03-2015')
    ;
    
    select * from rezervare;
    +----+------------+------------+
    | id | check_in   | checkout   |
    +----+------------+------------+
    |  1 | 12-03-2015 | 12-03-2015 |
    |  2 | 13-03-2015 | 13-03-2015 |
    |  3 | 14-03-2015 | 14-03-2015 |
    |  4 | 15-03-2015 | 15-03-2015 |
    |  5 | 16-03-2015 | 16-03-2015 |
    |  6 | 17-03-2015 | 17-03-2015 |
    |  7 | 19-03-2015 | 19-03-2015 |
    |  8 | 20-03-2015 | 20-03-2015 |
    +----+------------+------------+
    

    If your schema same as this, you can't use date function, because:

    select id, date(check_in), date(checkout) from rezervare ;
    +----+----------------+----------------+
    | id | date(check_in) | date(checkout) |
    +----+----------------+----------------+
    |  1 | NULL           | NULL           |
    |  2 | NULL           | NULL           |
    |  3 | NULL           | NULL           |
    |  4 | NULL           | NULL           |
    |  5 | NULL           | NULL           |
    |  6 | NULL           | NULL           |
    |  7 | NULL           | NULL           |
    |  8 | NULL           | NULL           |
    +----+----------------+----------------+
    

    But you can use between, in my example it works fine:

    select * from rezervare where checkout between '12-03-2015' and '15-03-2015';
    +----+------------+------------+
    | id | check_in   | checkout   |
    +----+------------+------------+
    |  1 | 12-03-2015 | 12-03-2015 |
    |  2 | 13-03-2015 | 13-03-2015 |
    |  3 | 14-03-2015 | 14-03-2015 |
    |  4 | 15-03-2015 | 15-03-2015 |
    +----+------------+------------+
    
    select * from rezervare where checkout > '14-03-2015';
    +----+------------+------------+
    | id | check_in   | checkout   |
    +----+------------+------------+
    |  4 | 15-03-2015 | 15-03-2015 |
    |  5 | 16-03-2015 | 16-03-2015 |
    |  6 | 17-03-2015 | 17-03-2015 |
    |  7 | 19-03-2015 | 19-03-2015 |
    |  8 | 20-03-2015 | 20-03-2015 |
    +----+------------+------------+
    

    But true way - alter table and use correct date type.

    点赞 评论 复制链接分享
  • donglv9116 donglv9116 6年前

    Use the BETWEEN function:

    SELECT *
        FROM rezervare
        WHERE (date BETWEEN '2010-03-12' AND '2010-03-15')
    
    点赞 评论 复制链接分享
  • doujiao2443 doujiao2443 6年前

    In this case, you should use int to store the date as a timestamp. The PHP function date() could transform timestamp to date in the format you choose.

    点赞 评论 复制链接分享