返回未来7天过生日的 怎么写sql语句

Question

declare @demo table
(
did int identity(1,1),
dname char(1),
dbirth datetime
)insert @demo select 'a','2008-10-20'
union all select 'a','2004-10-23'
union all select 'a','2007-10-16'
union all select 'a','2006-10-17'select
*,
datepart(day,dateadd(day,7,getdate()))
from @demo
where datepart(month,dbirth) = datepart(month,dateadd(day,7,getdate()))
and datepart(day,dbirth) <= datepart(day,dateadd(day,7,getdate()))/**
1 a 2008-10-20 00:00:00.000 21
3 a 2007-10-16 00:00:00.000 21
4 a 2006-10-17 00:00:00.000 21
**/

Answer 1

--这样就可以，不用那列，那列是我刚才测试用的
select
*
from @demo
where datepart(month,dbirth) = datepart(month,dateadd(day,7,getdate()))
and datepart(day,dbirth) <= datepart(day,dateadd(day,7,getdate()))

Answer 2

select * from demo where datediff(day,getdate(),dbirth) between 1 and 7

Answer 3

create table #demo
(
    did int identity(1,1),
    dname char(1),
    dbirth datetime
)
insert #demo select 'a','2008-11-20'
union all select 'a','2004-10-23'
union all select 'a','2007-10-16'
union all select 'a','2006-10-17'
union all select 'a','2008-10-17'select * from #demo where dbirth<dateadd(d,7,getdate()) and year(dbirth)=year(getdate())

Answer 4

declare @demo table
(
    did int identity(1,1),
    dname char(1),
    dbirth datetime
)insert @demo select 'a','2008-10-20'
union all select 'a','2004-10-23'
union all select 'a','2007-10-16'
union all select 'a','2006-10-17'
Select * from @demo
Where DateAdd(year,Year(Getdate())-Year(dbirth),dbirth)
Between Convert(Varchar(10),Getdate(),120)
And Convert(Varchar(10),Getdate()+7,120)
/*
(影響 4 個資料列)did         dname dbirth                                                 
----------- ----- ------------------------------------------------------ 
1           a     2008-10-20 00:00:00.000
3           a     2007-10-16 00:00:00.000
4           a     2006-10-17 00:00:00.000(影響 3 個資料列)*/

Answer 5

select * 
from demo 
where month(dbirth)=month(getdate()) And day(dbirth)- day(getdate()) <=7 
And day(dbirth)- day(getdate()) >0

Answer 6

select * from demo where datediff(dd,getdate(),datename(yy,getdate())+'-'+convert(varchar(5),dbirth,110)) between 1 and 7

Answer 7

select * from #demo where dbirth<dateadd(d,7,getdate()) and dbirth>=getdate() 

Answer 8

2月29日出生的，在2009年哪一天过生日呀。
如果是3月1日的话，可以这这样写。
select * from demo where datediff(dd,getdate(),dateadd(year,dbirth,datediff(year,getdate(),dbirth))) between 1 and  7

Answer 9

select * from demo where (month(dbirth) between month(getdate()) and month(dateadd(day,7,getdate())))
and  (day(dbirth) between day(getdate()) and day(dateadd(day,7,getdate())))

Answer 10

select * from #demo where day(dbirth)<day(dateadd(day,7,getdate())) and month(dbirth)=month(getdate())

Answer 11

考虑跨年和跨月的，写了一个，但是语法检查不通过，请大家指点一下：select * from demo
where 
(case year(getdate()+7)-year(getdate()) 
when 0 then year(dbirth)=year(getdate()) 
when 1 then year(dbirth)=year(getdate())+1 
else 1=1
end)
and
(case (month(getdate()+7)-month(getdate())) 
when 0 then month(dbirth)=month(getdate())
when 1 then month(dbirth)=month(getdate())+1
else 1=1
end)
and
day(dbirth) in(day(getdate()+1),day(getdate()+2),day(getdate()+3),day(getdate()+4),day(getdate()+5),day(getdate()+6),day(getdate()+7))

Answer 12

SELECT *,convert(datetime,convert(nvarchar(4),year(getdate()))+'-'+convert(nvarchar(4),month(dbirth))+'-'+convert(nvarchar(4),day(dbirth))) as newbirth
FROM demo
where convert(datetime,convert(nvarchar(4),year(getdate()))+'-'+convert(nvarchar(4),month(dbirth))+'-'+convert(nvarchar(4),day(dbirth))) >= getdate()
 and convert(datetime,convert(nvarchar(4),year(getdate()))+'-'+convert(nvarchar(4),month(dbirth))+'-'+convert(nvarchar(4),day(dbirth))) <= getdate() + 7

Answer 13

SELECT *,convert(datetime,convert(nvarchar(4),year(getdate()))+'-'+convert(nvarchar(4),month(dbirth))+'-'+convert(nvarchar(4),day(dbirth))) as newbirth
FROM demo
where convert(datetime,convert(nvarchar(4),year(getdate()))+'-'+convert(nvarchar(4),month(dbirth))+'-'+convert(nvarchar(4),day(dbirth))) >= getdate()
 and convert(datetime,convert(nvarchar(4),year(getdate()))+'-'+convert(nvarchar(4),month(dbirth))+'-'+convert(nvarchar(4),day(dbirth))) <= getdate() + 7

Answer 14

declare @t table(dd datetime)
insert @t select '1902-12-24'
union select '1912-12-25'
union select '1922-12-26'
union select '1932-12-27'
union select '1942-12-28'
union select '1952-12-29'
union select '1962-12-30'
union select '1972-12-31'
union select '1982-01-01'
union select '1992-01-02'
union select '2002-01-03'
union select '2003-05-03'
union select '2004-02-29'
declare @dd datetime
select @dd = '2007-12-26' --今天
select *  from @t where (datediff(dd,@dd,dateadd(year,datediff(year,dd,@dd),dd)))   between 1 and  7
                     or (datediff(dd,@dd,dateadd(year,datediff(year,dd,@dd)+1,dd))) between 1 and  7
/*
dd                                                     
------------------------------------------------------ 
1932-12-27 00:00:00.000
1942-12-28 00:00:00.000
1952-12-29 00:00:00.000
1962-12-30 00:00:00.000
1972-12-31 00:00:00.000
1982-01-01 00:00:00.000
1992-01-02 00:00:00.000
*/