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()
)
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()
)