生日提醒,用SQL怎么写?谢谢大家

[xinpplive]

表:staff
staff_id staff_name staff_time
1 刘德华 1981-5-1
2 张飞 1950-11-16
3 王小五 1880-1-1
在存储过程中，
如果得到当日|当月|提前3天提醒|过生日的人员,除1880-1-1外

[wuhan_boy]

顶哈

[tomyuansir]

修改3天内的：


3天内生日的：

select * from staff where datediff(day,getdate(),staff_time)<=3

[tomyuansir]

select * from staff where datediff(day,getdate(),staff_time)<=3 and datediff(day,getdate(),staff_time)>=0

[SoftwKLC]

tomyuansir() ( ) 信誉：100
select * from staff where datediff(day,getdate(),staff_time)<=3 and datediff(day,getdate(),staff_time)>=0
----你的不对,如果一个是2005-09-19出生

Declare @d datetime
Set @d='2005-09-19' /*一个人的出生日期*/
Select Datediff(dd,getdate(),@d)
---结果是 -730
它并不在>=0 And <=3范围内,但是他在今天也是生日

[sp4]

Declare @Date DateTime
Set @Date = GetDate()

Select * From Staff
Where DateDiff(Day,DateAdd(Year,Staff_Time,DateDiff(year,Staff_Time,@Date())),@Date)<=3

[sp4]

Create table Staff(id int identity(1,1),Staff_Time Datetime)
go
insert into Staff(Staff_Time)
select '20020901' union all
select '20010917' union all
select '19820701' union all
select '19730912' union all
select '19761206' union all
select '19680918'
go

Select * From Staff
Where
DateDiff(Day,DateAdd(Year,DateDiff(year,Staff_Time,@Date),Staff_Time),@Date) between 0 and 3

Drop table Staff

------------------------------------------------------
id Staff_Time
----------- ------------------------------------------------------
2 2001-09-17 00:00:00.000
6 1968-09-18 00:00:00.000

（所影响的行数为 2 行）

[sp4]

Create table Staff(id int identity(1,1),Staff_Time Datetime)
go
insert into Staff(Staff_Time)
select '20020901' union all
select '20010917' union all
select '19820701' union all
select '19730912' union all
select '19761206' union all
select '19680918'
go

declare @Date datetime
set @Date = GetDate()

Select * From Staff
Where
DateDiff(Day,DateAdd(Year,DateDiff(year,Staff_Time,@Date),Staff_Time),@Date) between 0 and 3

Drop table Staff

------------------------------------------------------
id Staff_Time
----------- ------------------------------------------------------
2 2001-09-17 00:00:00.000
6 1968-09-18 00:00:00.000

（所影响的行数为 2 行）

[SoftwKLC]

谢谢SoftwKLC(自由的飞鸟(卢成))
三天之内不带OR,用AND,应该怎么做,能不能
当前时间-生日时间=<3,我对这个不太懂.
----------------------------------------
用<=3 ?应该还要大于0

select
*
from
staff
where datediff(dd,getdate(),
cast(rtrim(datepart(yy,getdate()))+'-'+
rtrim(datepart(mm,staff_time))+'-'+
rtrim(datepart(dd,staff_time)) as datetime)) between 0 And 3

[playyuer]

where datediff(day,getdate(),dateadd(year,datediff(year,staff_time,getdate()),staff_time))<3

[zouzhou1984]

select * from month(staff_time) = month(dateadd(day,3,getdate())) and day(staff_time) = day(dateadd(day,-3,getdate()))

[tomyuansir]

SoftwKLC(自由的飞鸟(卢成)) ( ) 信誉：100
谢谢指出 错误 谢谢！！

[sp4]

--上面的有一个问题，就是没有能避免闰年的2-29日的问题
--需要再增加个日期判断就完整了
declare @Date datetime
set @Date = GetDate()

Select * From Staff
Where
DateDiff(Day,DateAdd(Year,DateDiff(year,Staff_Time,@Date),Staff_Time),@Date) between 0 and 3
--增加的日期判断，避免2月29日的生日提醒遗漏或多余提醒
and datepart(day,Staff_Time)=datepart(day,dateadd(year,datediff(year,Staff_Time,@Date),Staff_Time))

Drop table Staff

[数字化从业者老高]

问题应该解决了，主要就是dateadd的用法。

[honey52570]

mark

[airzen]

好贴!
顶一下.

[qwerrewq888]

正想找呢，太谢谢了

[gavin_yao09]

16楼的帖子我这个严重过，没有问题！

select
*
from
staff
where datediff(dd,getdate(),
cast(rtrim(datepart(yy,getdate()))+ '- '+
rtrim(datepart(mm,staff_time))+ '- '+
rtrim(datepart(dd,staff_time)) as datetime)) between 0 And 3

[daocaowe]

mark

[daocaowe]

16楼的不行 没考虑跨年的 还有2-29就出错

[mxh691]

--解决闰年2月29日转换报错问题
if(datepart(mm,getdate())='2' and datepart(dd,getdate())='29')
begin
  select * from client c   
where datediff(dd, cast(rtrim(datepart(yy,c.ContactBrithday))+'-'+  
  rtrim(datepart(mm,getdate()))+'-'+  
  rtrim(datepart(dd,getdate())-1) as datetime),c.ContactBrithday) between 0 And 5
end
else
  begin
  select * from client c   
where datediff(dd, cast(rtrim(datepart(yy,c.ContactBrithday))+'-'+  
  rtrim(datepart(mm,getdate()))+'-'+  
  rtrim(datepart(dd,getdate())) as datetime),c.ContactBrithday) between 0 And 5
  or
  --解决跨年问题
  datediff(dd, cast(rtrim(datepart(yy,c.ContactBrithday)-1)+'-'+  
  rtrim(datepart(mm,getdate()))+'-'+  
  rtrim(datepart(dd,getdate())) as datetime),c.ContactBrithday) between 0 And 5
  end

--思路是把当前日期的年份用生日年份替换，这样在转换日期2-29这样如果报错的话只要通过判断
当前日期是否刚好2月29日就成了