呵呵
--当天
select *
from 表
where (month(getdate()) = month(birthDay) and day(getdate()) = day(birthDay))
--本月、下月
select *
from 表
where month(birthDay) - month(getdate()) in (0,1,-11)
--当天
select *
from 表
where (month(getdate()) = month(birthDay) and day(getdate()) = day(birthDay))
--本月、下月
select *
from 表
where month(birthDay) - month(getdate()) in (0,1,-11)
declare @Employee table(id int,birthday datetime)
insert @Employee
select 1,'2006-12-26' union all
select 2,'2006-12-27' union all
select 3,'2007-01-01' union all
select 4,'2007-01-11' union all
select 5,'2007-02-01'----查询
select *,'今天过生日' from @Employee where datediff(dd,birthday,getdate()) = 0
union all
select *,'本月过生日' from @Employee where datediff(month,birthday,getdate()) = 0
union all
select *,'下月过生日' from @Employee where datediff(month,birthday,getdate()) = -1/*结果
id birthday
------------------------------------------------
2 2006-12-27 00:00:00.000 今天过生日
1 2006-12-26 00:00:00.000 本月过生日
2 2006-12-27 00:00:00.000 本月过生日
3 2007-01-01 00:00:00.000 下月过生日
4 2007-01-11 00:00:00.000 下月过生日
*/
不过hellowork(一两清风) 这位大哥写的我怎么看不明白了啊。
能不能说清楚点啊,小弟我初学。不好意思啊
select * from Employees where right(convert(varchar(10),birthday,120),5) = right(convert(varchar(10),getdate(),120),5)
本月
select * from Employees where month(birthday) = month(getdate())
下月
select * from Employees where (month(birthday) = month(getdate())+1 and month(getdate()) < 12) or (month(birthday) = 1 and month(getdate()) = 12)
----创建测试数据
declare @Employee table(id int,birthday datetime)
insert @Employee
select 1,'1996-12-26' union all
select 2,'1997-12-27' union all
select 3,'1995-01-01' union all
select 4,'1995-01-11' union all
select 5,'1996-02-01'----查询
select *,'今天过生日' from @Employee where month(birthday) = month(getdate()) and day(birthday) = day(getdate())
union all
select *,'本月过生日' from @Employee where month(birthday) = month(getdate())
union all
select *,'下月过生日' from @Employee where month(birthday) - month(getdate()) in(1,-11)/*结果
id birthday
2 1997-12-27 00:00:00.000 今天过生日
1 1996-12-26 00:00:00.000 本月过生日
2 1997-12-27 00:00:00.000 本月过生日
3 1995-01-01 00:00:00.000 下月过生日
4 1995-01-11 00:00:00.000 下月过生日
*/
from @Employee
where month(birthday) = month(dateadd(month,1,getdate()))