不会出现那种情况的,另外楼主你的判断截取有些多余了,直接用datetime做判断就好了,还有不需要用到%y,直接用YEAR()函数啊。你的sqsl可以改写成如下:
SELECT * FROM user WHERE DATEDIFF (CAST(CONCAT(YEAR(NOW()),DATE_FORMAT(birthday,'-%m-%d')) AS DATETIME), CAST(CURRENT_DATE() AS DATETIME)) IN (0,1);请再参考如下的判断逻辑结果,觉得逻辑蛮清晰的:mysql> SELECT DATEDIFF (CAST(CONCAT(YEAR(NOW()),DATE_FORMAT('2014-08-30','-%m-%d')) AS DATETIME), CAST(CURRENT_DATE() AS DATETIME)) AS XiangCha_TianShu ;
+------------------+
| XiangCha_TianShu |
+------------------+
| 0 |
+------------------+
1 row in set (0.00 sec)mysql> SELECT DATEDIFF (CAST(CONCAT(YEAR(NOW()),DATE_FORMAT('2014-08-31','-%m-%d')) AS DATETIME), CAST(CURRENT_DATE() AS DATETIME)) AS XiangCha_TianShu ;
+------------------+
| XiangCha_TianShu |
+------------------+
| 1 |
+------------------+
1 row in set (0.00 sec)mysql> SELECT DATEDIFF (CAST(CONCAT(YEAR(NOW()),DATE_FORMAT('2014-09-01','-%m-%d')) AS DATETIME), CAST(CURRENT_DATE() AS DATETIME)) AS XiangCha_TianShu ;
+------------------+
| XiangCha_TianShu |
+------------------+
| 2 |
+------------------+
1 row in set (0.00 sec)mysql> SELECT DATEDIFF (CAST(CONCAT(YEAR(NOW()),DATE_FORMAT('2014-08-29','-%m-%d')) AS DATETIME), CAST(CURRENT_DATE() AS DATETIME)) AS XiangCha_TianShu ;
+------------------+
| XiangCha_TianShu |
+------------------+
| -1 |
+------------------+
1 row in set (0.00 sec)mysql>
SELECT * FROM user WHERE DATEDIFF (CAST(CONCAT(YEAR(NOW()),DATE_FORMAT(birthday,'-%m-%d')) AS DATETIME), CAST(CURRENT_DATE() AS DATETIME)) IN (0,1);请再参考如下的判断逻辑结果,觉得逻辑蛮清晰的:mysql> SELECT DATEDIFF (CAST(CONCAT(YEAR(NOW()),DATE_FORMAT('2014-08-30','-%m-%d')) AS DATETIME), CAST(CURRENT_DATE() AS DATETIME)) AS XiangCha_TianShu ;
+------------------+
| XiangCha_TianShu |
+------------------+
| 0 |
+------------------+
1 row in set (0.00 sec)mysql> SELECT DATEDIFF (CAST(CONCAT(YEAR(NOW()),DATE_FORMAT('2014-08-31','-%m-%d')) AS DATETIME), CAST(CURRENT_DATE() AS DATETIME)) AS XiangCha_TianShu ;
+------------------+
| XiangCha_TianShu |
+------------------+
| 1 |
+------------------+
1 row in set (0.00 sec)mysql> SELECT DATEDIFF (CAST(CONCAT(YEAR(NOW()),DATE_FORMAT('2014-09-01','-%m-%d')) AS DATETIME), CAST(CURRENT_DATE() AS DATETIME)) AS XiangCha_TianShu ;
+------------------+
| XiangCha_TianShu |
+------------------+
| 2 |
+------------------+
1 row in set (0.00 sec)mysql> SELECT DATEDIFF (CAST(CONCAT(YEAR(NOW()),DATE_FORMAT('2014-08-29','-%m-%d')) AS DATETIME), CAST(CURRENT_DATE() AS DATETIME)) AS XiangCha_TianShu ;
+------------------+
| XiangCha_TianShu |
+------------------+
| -1 |
+------------------+
1 row in set (0.00 sec)mysql>
where DATE_FORMAT(birthday,'%m%d') = DATE_FORMAT(curdate(),'%m%d') or DATE_FORMAT(birthday,'%m%d') = DATE_FORMAT(curdate()+ interval 1 day,'%m%d')
1,假如明年是闰年2004年,生日是2004-01-01,当前日期就是2003-12-31,你执行如下判断,相差日期也是1。
mysql> SELECT DATEDIFF (CAST(CONCAT('2004-01-01',DATE_FORMAT('2004-01-01','-%m-%d')) AS DATETIME), CAST('2003-12-31' AS DATETIME));
+------------------------------------------------------------------------------------------------------------------------+
| DATEDIFF (CAST(CONCAT('2004-01-01',DATE_FORMAT('2004-01-01','-%m-%d')) AS DATETIME), CAST('2003-12-31' AS DATETIME)) |
+------------------------------------------------------------------------------------------------------------------------+
| 1 |
+------------------------------------------------------------------------------------------------------------------------+
1 ROW IN SET (0.00 sec)2,假如明年不是闰年,生日是2007-01-01,当前日期是2006-12-31,你执行如下判断,相差日期也是1。
SELECT DATEDIFF (CAST(CONCAT('2007-01-01',DATE_FORMAT('2007-01-01','-%m-%d')) AS DATETIME), CAST('2006-12-31' AS DATETIME));
mysql> SELECT DATEDIFF (CAST(CONCAT('2007-01-01',DATE_FORMAT('2007-01-01','-%m-%d')) AS DATETIME), CAST('2006-12-31' AS DATETIME));
+------------------------------------------------------------------------------------------------------------------------+
| DATEDIFF (CAST(CONCAT('2007-01-01',DATE_FORMAT('2007-01-01','-%m-%d')) AS DATETIME), CAST('2006-12-31' AS DATETIME)) |
+------------------------------------------------------------------------------------------------------------------------+
| 1 |
+------------------------------------------------------------------------------------------------------------------------+
1 ROW IN SET (0.00 sec)mysql>
mysql>
年份有闰年非闰年区别之分,楼主看我下面的2个年份判断过程:1,假如明年是闰年2004年,生日是2004-01-01,当前日期就是2003-12-31,你执行如下判断,相差日期也是1。
mysql> SELECT DATEDIFF (CAST(CONCAT('2004-01-01',DATE_FORMAT('2004-01-01','-%m-%d')) AS DATETIME), CAST('2003-12-31' AS DATETIME));
+------------------------------------------------------------------------------------------------------------------------+
| DATEDIFF (CAST(CONCAT('2004-01-01',DATE_FORMAT('2004-01-01','-%m-%d')) AS DATETIME), CAST('2003-12-31' AS DATETIME)) |
+------------------------------------------------------------------------------------------------------------------------+
| 1 |
+------------------------------------------------------------------------------------------------------------------------+
1 ROW IN SET (0.00 sec)2,假如明年不是闰年,生日是2007-01-01,当前日期是2006-12-31,你执行如下判断,相差日期也是1。
SELECT DATEDIFF (CAST(CONCAT('2007-01-01',DATE_FORMAT('2007-01-01','-%m-%d')) AS DATETIME), CAST('2006-12-31' AS DATETIME));
mysql> SELECT DATEDIFF (CAST(CONCAT('2007-01-01',DATE_FORMAT('2007-01-01','-%m-%d')) AS DATETIME), CAST('2006-12-31' AS DATETIME));
+------------------------------------------------------------------------------------------------------------------------+
| DATEDIFF (CAST(CONCAT('2007-01-01',DATE_FORMAT('2007-01-01','-%m-%d')) AS DATETIME), CAST('2006-12-31' AS DATETIME)) |
+------------------------------------------------------------------------------------------------------------------------+
| 1 |
+------------------------------------------------------------------------------------------------------------------------+
1 ROW IN SET (0.00 sec)mysql>
mysql>
所以综上所述,生日在1月1日的,也能提前通知到,不用担心的,楼主的问题在于DATE_FORMAT(NOW(),'%y')这一点上不能取当前日期NOW()的年份,要取生日那一年的年份数。
哈哈简单啊,用between 0 and 3啊,嘿嘿。
哈哈简单啊,用between 0 and 3啊,嘿嘿。把3换成你想要的天数N, between 0 and N蛮不错的吧
哈哈简单啊,用between 0 and 3啊,嘿嘿。
又回到初始的问题了,无法跨年
哈哈简单啊,用between 0 and 3啊,嘿嘿。
又回到初始的问题了,无法跨年
可以跨年啊,你用我的判断条件。你的逻辑不正确所以不能跨年。
正确的逻辑是:
SELECT DATEDIFF (CAST(CONCAT(生日字段,DATE_FORMAT(生日字段,'-%m-%d')) AS DATETIME), CAST(当天日期 AS DATETIME));
-- ------
而你的SQL是:
DATEDIFF(CAST(CONCAT(DATE_FORMAT(NOW(),'%y'),DATE_FORMAT(birthday,'-%m-%d'))AS DATE),CAST(DATE_FORMAT(NOW(),'%y-%m-%d') AS DATE)) <=1
你的判断逻辑是:
DATEDIFF(CAST(CONCAT(DATE_FORMAT(当天日期,'%y'),DATE_FORMAT(生日字段,'-%m-%d'))AS DATE),CAST(DATE_FORMAT(当天日期,'%y-%m-%d') AS DATE)) <=1你的逻辑不对,才有跨年的问题,再去看看我在5楼的留言的最后黑体和红色粗体留言中的: DATE_FORMAT(NOW(),'%y')这一点上不能取当前日期NOW()的年份,要取生日那一年的年份数。
我知道问题所在了,再加2个跨年的值,就OK了,你试试吧
SELECT DATEDIFF (CAST(CONCAT(YEAR(NOW()),DATE_FORMAT('1980-01-01','-%m-%d')) AS DATETIME), CAST(NOW() AS DATETIME)) IN (0,1,-365,-364);这-365,-364 是生日是1月1日的跨年当天查询所考虑的细节。这回应该OK了。
我知道问题所在了,再加2个跨年的值,就OK了,你试试吧
SELECT DATEDIFF (CAST(CONCAT(YEAR(NOW()),DATE_FORMAT(birthday,'-%m-%d')) AS DATETIME), CAST(NOW() AS DATETIME)) IN (0,1,-365,-364);这-365,-364 是生日是1月1日的跨年当天查询所考虑的细节。这回应该OK了。
针对跨年再添加一个条件就OK了,添加条件是DATEDIFF(CAST(CONCAT(YEAR(NOW())+1,DATE_FORMAT(birthday,'-%m-%d'))AS DATE),CAST(DATE_FORMAT(NOW(),'%y-%m-%d') AS DATE)) BETWEEN 0 AND 1然后N天,就是最近N天过生日的,我们这里N=1
SELECT * FROM USER WHERE
DATEDIFF(CAST(CONCAT(YEAR(NOW(),DATE_FORMAT(birthday,'-%m-%d'))AS DATE),CAST(DATE_FORMAT(NOW(),'%y-%m-%d') AS DATE)) BETWEEN 0 AND 1
OR /*后面的是判断跨年的问题*/
DATEDIFF(CAST(CONCAT(YEAR(NOW())+1,DATE_FORMAT(birthday,'-%m-%d'))AS DATE),CAST(DATE_FORMAT(NOW(),'%y-%m-%d') AS DATE)) BETWEEN 0 AND 1
;
我已经搞定了啊,嘿嘿,看下面:针对跨年再添加一个条件就OK了,添加条件是DATEDIFF(CAST(CONCAT(YEAR(NOW())+1,DATE_FORMAT(birthday,'-%m-%d'))AS DATE),CAST(DATE_FORMAT(NOW(),'%y-%m-%d') AS DATE)) BETWEEN 0 AND 1然后N天,就是最近N天过生日的,我们这里N=1
SELECT * FROM USER WHERE
DATEDIFF(CAST(CONCAT(YEAR(NOW(),DATE_FORMAT(birthday,'-%m-%d'))AS DATE),CAST(DATE_FORMAT(NOW(),'%y-%m-%d') AS DATE)) BETWEEN 0 AND 1
OR /*后面的是判断跨年的问题*/
DATEDIFF(CAST(CONCAT(YEAR(NOW())+1,DATE_FORMAT(birthday,'-%m-%d'))AS DATE),CAST(DATE_FORMAT(NOW(),'%y-%m-%d') AS DATE)) BETWEEN 0 AND 1
整理了一下思路,把你的生日跨年问题,写在blog里面了,你可以参考:
http://blog.itpub.net/26230597/viewspace-1260767/
http://blog.csdn.net/mchdba/article/details/38952033
问得好,我去研究一下子。这个不是让程序员来研究的,这个是人事部自己要定义的东西。比如有些单位直接就是把 2.29 合并在 2.28 中一起过生日。 这玩意类似哪一天是公司定义的每周第一天一样,闰年问题我已经搞定了,SQL如下:SELECT * FROM ali_users WHERE
DATEDIFF(CAST(CONCAT(YEAR(NOW()),DATE_FORMAT(birthday,'-%m-%d'))AS DATE),CAST(DATE_FORMAT(NOW(),'%y-%m-%d') AS DATE)) BETWEEN 0 AND 4
OR/* or后面的是捎带解决跨年问题*/
DATEDIFF(CAST(CONCAT(YEAR(NOW())+1,DATE_FORMAT(birthday,'-%m-%d'))AS DATE),CAST(DATE_FORMAT(NOW(),'%y-%m-%d') AS DATE)) BETWEEN 0 AND 4
OR /*补充闰年2月29日的生日问题*/
(
f_not_leap_year(YEAR(NOW()))
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(NOW(),'-%m-%d')) AS DATE))
BETWEEN 0 AND 4
);刚弄完,等明天把详细测试过程写在blog里面,再贴出来。