Ответы пользователя по тегу MySQL
  • Mysql как выбрать ближайший день рождения?

    abyrvalg
    @abyrvalg
    Вроде бы всё учтено:
    select t2.birthday,
          date_add(
              date_add(
                  makedate(b_year, 1),
                  interval month(t2.birthday) - 1 month
                ),
              interval
                if(month(t2.birthday) = 2 and day(t2.birthday) = 29 and b_year % 4 <> 0,
                   28,
                  day(t2.birthday)
                )
                - 1 day
          ) nearest_birthday
    from (
          select t.birthday,
                 year(@now) +
                 if(100 * month(t.birthday) + day(t.birthday) < 100 * month(@now) + day(@now),
                   1,
                   0
                 ) b_year
          from t,
               (select @now := '2012-12-14') vars   -- <<== CURRENT DATE
        ) t2
    having timestampdiff(day, @now, nearest_birthday) <= 85 -- <<== WINDOW
    order by nearest_birthday
    ;


    Проверялось на вот этих данных:

    drop table if exists t;
    create table t (
      birthday date not null
    );
    
    insert into t(birthday) values('1980-01-01');
    insert into t(birthday) values('1980-01-15');
    insert into t(birthday) values('1981-02-01');
    insert into t(birthday) values('1981-02-15');
    insert into t(birthday) values('2000-02-28');
    insert into t(birthday) values('2000-02-29');
    insert into t(birthday) values('1982-03-01');
    insert into t(birthday) values('1982-03-15');
    insert into t(birthday) values('1983-04-01');
    insert into t(birthday) values('1983-04-15');
    insert into t(birthday) values('1983-05-01');
    insert into t(birthday) values('1983-05-15');
    insert into t(birthday) values('1983-06-01');
    insert into t(birthday) values('1983-06-15');
    insert into t(birthday) values('1983-07-01');
    insert into t(birthday) values('1983-07-15');
    insert into t(birthday) values('1983-08-01');
    insert into t(birthday) values('1983-08-15');
    insert into t(birthday) values('1983-09-01');
    insert into t(birthday) values('1983-09-15');
    insert into t(birthday) values('1983-10-01');
    insert into t(birthday) values('1983-10-15');
    insert into t(birthday) values('1983-11-01');
    insert into t(birthday) values('1983-11-15');
    insert into t(birthday) values('1983-12-01');
    insert into t(birthday) values('1983-12-15');
    Ответ написан