SQL查询XX天内员工的生日

SQL查询XX天内员工的生日  


1. 查询一周


SELECT FullName,(dateadd(year,datediff(year,birthday,getdate()),birthday)) AS Nbirthday
FROM    PMEmployeesInfo  
WHERE  (dateadd(year,datediff(year,birthday,getdate()),birthday)) BETWEEN getdate() AND getdate()+7




2.自定义时间段


SELECT FullName,(dateadd(year,datediff(year,birthday,getdate()),birthday))AS Nbirthday 
FROM PMEmployeesInfo
WHERE
(dateadd(year,datediff(year,birthday,'2012-12-05'),birthday) BETWEEN '2012-12-05' AND '2013-01-14')
or
(dateadd(year,datediff(year,birthday,'2013-01-14'),birthday) BETWEEN '2012-12-05' AND '2013-01-14')
ORDER BY Nbirthday ASC


 


3 函数解释


2012-12-05是起始日期，2013-01-14是结束日期


datediff(year,birthday,getdate())这句意思是两个日期相减得到年数


dateadd(year,datediff(year,birthday,getdate()),birthday)这句意思是将birthday的年份加上相减的年数


如果birthday是1989-05-05，利用这句话得到的就是2013-05-05


自定义时间段的好处不仅仅是自定义，最重要的是解决了跨年时查询不准的情况，如想查询2012年12月20号到2013年1月20号的人员生日。




转摘自：http://blog.163.com/heaver_1989/blog/static/1846043382013010102442454/

整理： 查多少天内生日的所有信息（正数往后算，负数往前算）

SELECT GETDATE(),DATEADD(day,-40,getdate()),birthday ,(dateadd(year,datediff(year,birthday,getdate()),birthday))AS Nbirthday ,
 DATEADD(year,datediff(year,birthday,DATEADD(day,-40,getdate())),birthday),
 datediff(year,birthday,getdate()),DATEDIFF(year,birthday,DATEADD(day,-40,getdate()))
 FROM Table
where ISNULL(birthday,'') <> '' 
AND 
(
 --天数为正
 (dateadd(year,datediff(year,birthday,getdate()),birthday)) BETWEEN GETDATE() AND DATEADD(day,-40,getdate())
  OR 
 (dateadd(year,datediff(year,birthday,DATEADD(day,-40,getdate())),birthday))  BETWEEN GETDATE() AND DATEADD(day,-40,getdate())
 
 OR
 --天数为负
 (dateadd(year,datediff(year,birthday,getdate()),birthday)) BETWEEN  DATEADD(day,-40,getdate())and GETDATE()
  OR 
 (dateadd(year,datediff(year,birthday,DATEADD(day,-40,getdate())),birthday))  BETWEEN DATEADD(day,-40,getdate()) AND GETDATE()
)