【讨论】生日提醒怎么查询最好？

[冷箫轻笛]

要求提前30天提醒员工生日，大家讨论一下看都可以怎么写。
我写的很繁琐


SELECT BIRTHDAY FROM Employee
where (datediff(day,getdate(),dateadd(year,datediff(year,birthday,getdate()),birthday)) >= 0
and datediff(day,getdate(),dateadd(year,datediff(year,birthday,getdate()),birthday))<= 30 )
or (datediff(day,getdate(),dateadd(year,datediff(year,birthday,getdate())+1,birthday)) >= 0
and datediff(day,getdate(),dateadd(year,datediff(year,birthday,getdate())+1,birthday)) <= 30 )

[冷箫轻笛]

表 Employee 生日字段 BIRTHDAY

[子陌红尘]

select
BIRTHDAY
from
Employee
where
datediff(day,getdate(),rtrim(year(getdate()) )+right(convert(char(10),BIRTHDAY,120),6)) between 0 and 30
or
datediff(day,getdate(),rtrim(year(getdate())+1)+right(convert(char(10),BIRTHDAY,120),6)) between 0 and 30

[WangZWang]

Select * from Employee
Where datediff(day,dateadd(year,datediff(year,birthday,getdate()),birthday),getdate())<=30

[WangZWang]

Select * from Employee
Where datediff(day,dateadd(year,datediff(year,birthday,getdate()),birthday),
getdate()) between 0 and 30

[csdnweii]

select
BIRTHDAY
from
Employee
where
datediff(day,getdate(),rtrim(year(getdate()))+'-'+month(BIRTHDAY)+'-'+(BIRTHDAY)) between 0 and 30
or
datediff(day,getdate(),rtrim(year(getdate())+1)+'-'+month(BIRTHDAY)+'-'+(BIRTHDAY)) between 0 and 30

[csdnweii]

select
BIRTHDAY
from
Employee
where
datediff(day,getdate(),rtrim(year(getdate()))+'-'+month(BIRTHDAY)+'-'+day(BIRTHDAY)) between 0 and 30
or
datediff(day,getdate(),rtrim(year(getdate())+1)+'-'+month(BIRTHDAY)+'-'+day(BIRTHDAY)) between 0 and 30

[冷箫轻笛]

晕，犯了一个低级错误！呵呵

只有这种算法了吗？

to:WangZWang(先来)
如果该员工的生日是1月份的，比如说1月1号，那么这个日期在头年的12月就应该提示的

[WangZWang]

Select birthday,* from Employee
Where datediff(day,getdate(),dateadd(year,
datediff(year,birthday,getdate()),birthday)) between 0 and 30

[fcuandy]

光比较时间差我觉得是有问题的.
某个人 2000-2-29出生,你在 2001-2-1提示他过28天他生日,而实际上他命不好,这年他根本没生日过

[冷箫轻笛]

to:fcuandy(老菜鸟)
有理！！！
我看看这种情况用不用考虑哈。

[冷箫轻笛]

还是子陌老大写的最好！
揭帖了！

[冷箫轻笛]

哦，先等等
子陌老大的也存在 2月29的情况

老菜鸟说的情况可以不用考虑，但是如果有个人是2月29的生日的话语句就会出错

[冷箫轻笛]

揭帖！

[zsforever]

Select birthday,* from Employee
Where isdate(case when right(convert(varchar(10),getdate(),120),6)>right(convert(varchar(10),BIRTHDAY,120),6)
then rtrim(year(getdate())) else rtrim(year(getdate())-1) end + right(convert(varchar(10),BIRTHDAY,120),6))=1 and
datediff(day,
case when right(convert(varchar(10),getdate(),120),6)>right(convert(varchar(10),BIRTHDAY,120),6)
then rtrim(year(getdate())) else rtrim(year(getdate())-1) end + right(convert(varchar(10),BIRTHDAY,120),6),
getdate()) <= 30

[zsforever]

好象好要考虑'2002-01-29'这样的特殊情况吧