最近一周生日提醒

使用MySQL資料庫查詢最近一周生日 SELECT  DATE_FORMAT(birthday,'%c-%e' ) AS newbirthday FROM `t_contact`

WHERE   DATEDIFF( DATE_FORMAT(birthday, concat(year(now()),'-%m-%d')), date(now()) ) < 7

and   DATEDIFF( DATE_FORMAT(birthday, concat(year(now()),'-%m-%d')),date(now())) >= 0

ORDER BY newbirthday ASC   這個查詢解決了跨月的問題，但沒有解決跨年的問題，要想解決跨年的問題   SELECT  DATE_FORMAT(birthday,'%c-%e' ) AS newbirthday FROM `t_contact`

WHERE (DATEDIFF( DATE_FORMAT(birthday, concat(year(now()),'-%m-%d')), date(now()) ) < 7

and DATEDIFF( DATE_FORMAT(birthday, concat(year(now()),'-%m-%d')),date(now())) >= 0) or (DATEDIFF( DATE_FORMAT(birthday, concat(year(now())+1,'-%m-%d')), date(now()) ) < 7

and DATEDIFF( DATE_FORMAT(birthday, concat(year(now())+1,'-%m-%d')),date(now())) >= 0)

ORDER BY newbirthday ASC   這樣就能查詢出我們需要的資料了，但如果想按最先到達的生日提醒排序(考慮到跨月和跨年的問題)，就需要将排序設定為 ORDER BY datediff(now(),birthday) DESC