[MySQL]--)查询5天之内过生日的同事中的闰年2月29日问题的解决过程 - 数据库编程

s WHERE
-> DATEDIFF(CAST(CONCAT(YEAR(\'2014-02-28 00:10:10\'),DATE_FORMAT(birthday,\'-%m-%d\'))AS DATE),CAST(DATE_FORMAT(\'2014-02-28 00:10:10\',\'%y-%m-%d\') AS DATE)) BETWEEN 0 AND 4
-> OR/* or后面的是捎带解决跨年问题*/
-> DATEDIFF(CAST(CONCAT(YEAR(\'2014-02-28 00:10:10\')+1,DATE_FORMAT(birthday,\'-%m-%d\'))AS DATE),CAST(DATE_FORMAT(\'2014-02-28 00:10:10\',\'%y-%m-%d\') AS DATE)) BETWEEN 0 AND 4
-> OR /*补充闰年2月29日的生日方法*/
-> (
-> f_not_leap_year(YEAR(\'2014-02-28 00:10:10\'))
-> AND DATE_FORMAT(birthday,\'-%m-%d\')=\'-02-29\'
-> AND DATEDIFF(CAST(CONCAT(\'2000\',DATE_FORMAT(birthday,\'-%m-%d\'))AS DATE),CAST(CONCAT(\'2000\',DATE_FORMAT(\'2000-02-28 00:10:10\',\'-%m-%d\')) AS DATE))
-> BETWEEN 0 AND 4
-> );
+----------+------------+-------------+
| username | birthday | iphone |
+----------+------------+-------------+
| Jeff | 1980-02-29 | 13998786549 |
| XiaoTeng | 1980-03-01 | 13998786549 |
| HeSheng | 1980-03-02 | 13998786549 |
| JingPan | 1980-03-03 | 13998786549 |
| WuHong | 1986-03-04 | 13998786549 |
| WeiYa | 1980-02-28 | 13998786549 |
+----------+------------+-------------+
6 rows in set, 2 warnings (0.00 sec)

mysql>
5.4.4 在闰年的时候，验证闰年2月29日生日，选择2004年闰年测试
把Step#2中的SQL的NOW()改成'2004-02-28 00:10:10'来进行测试，SQL如下所示：
SELECT * FROM ali_users WHERE
DATEDIFF(CAST(CONCAT(YEAR(\'2004-02-28 00:10:10\'),DATE_FORMAT(birthday,\'-%m-%d\'))AS DATE),CAST(DATE_FORMAT(\'2004-02-28 00:10:10\',\'%y-%m-%d\') AS DATE)) BETWEEN 0 AND 4
OR/* or后面的是捎带解决跨年问题*/
DATEDIFF(CAST(CONCAT(YEAR(\'2004-02-28 00:10:10\')+1,DATE_FORMAT(birthday,\'-%m-%d\'))AS DATE),CAST(DATE_FORMAT(\'2004-02-28 00:10:10\',\'%y-%m-%d\') AS DATE)) BETWEEN 0 AND 4

OR /*补充闰年2月29日的生日方法*/
(
f_not_leap_year(YEAR(\'2004-02-28 00:10:10\'))
AND DATE_FORMAT(birthday,\'-%m-%d\')=\'-02-29\'
AND DATEDIFF(CAST(CONCAT(\'2000\',DATE_FORMAT(birthday,\'-%m-%d\'))AS DATE),CAST(CONCAT(\'2000\',DATE_FORMAT(\'2004-02-28 00:10:10\',\'-%m-%d\')) ASDATE))
BETWEEN 0 AND 4
);
mysql> SELECT * FROM ali_users WHERE
-> DATEDIFF(CAST(CONCAT(YEAR(\'2004-02-28 00:10:10\'),DATE_FORMAT(birthday,\'-%m-%d\'))AS DATE),CAST(DATE_FORMAT(\'2004-02-28 00:10:10\',\'%y-%m-%d\') AS DATE)) BETWEEN 0 AND 4
-> OR/* or后面的是捎带解决跨年问题*/
-> DATEDIFF(CAST(CONCAT(YEAR(\'2004-02-28 00:10:10\')+1,DATE_FORMAT(birthday,\'-%m-%d\'))AS DATE),CAST(DATE_FORMAT(\'2004-02-28 00:10:10\',\'%y-%m-%d\') AS DATE)) BETWEEN 0 AND 4
-> OR /*补充闰年2月29日的生日方法*/
-> (
-> f_not_leap_year(YEAR(\'2004-02-28 00:10:10\'))
-> AND DATE_FORMAT(birthday,\'-%m-%d\')=\'-02-29\'
-> AND DATEDIFF(CAST(CONCAT(\'2000\',DATE_FORMAT(birthday,\'-%m-%d\'))AS DATE),CAST(CONCAT(\'2000\',DATE_FORMAT(\'2004-02-28 00:10:10\',\'-%m-%d\')) AS DATE))
-> BETWEEN 0 AND 4
-> );
+----------+------------+-------------+
| username | birthday | iphone |
+----------+------------+-------------+
| Jeff | 1980-02-29 | 13998786549 |
| XiaoTeng | 1980-03-01 | 13998786549 |
| HeSheng | 1980-03-02 | 13998786549 |
| JingPan | 1980-03-03 | 13998786549 |
| WeiYa | 1980-02-28 | 13998786549 |
+----------+------------+-------------+
5 rows in set (0.00 sec)

mysql>

[MySQL]--)查询5天之内过生日的同事中的闰年2月29日问题的解决过程(二)