一張表裡有個字段是:bir(表示出生日期如:1990-01-01)
現在求一個SQL語句,要實現的是這樣的,
Table: userinfo
userID bir
001 1990-01-01
002 2000-06-07
003 1980-10-11
004 1982-08-25
..................
假設當前時間為:2006-08-23,現在我想查詢七天內有生日的人,
得出結果應為:
userID bir
004 1982-08-25
這個怎麼寫啊??
現在求一個SQL語句,要實現的是這樣的,
Table: userinfo
userID bir
001 1990-01-01
002 2000-06-07
003 1980-10-11
004 1982-08-25
..................
假設當前時間為:2006-08-23,現在我想查詢七天內有生日的人,
得出結果應為:
userID bir
004 1982-08-25
這個怎麼寫啊??
from user_info
where bir between dateadd(day,-7,@date) and @date
from user_info
where datediff(day,getdate(),bir)<7
(
userid varchar(10),
bir datetime
)
insert userinfo
select '001', '1990-01-01' union all
select '002', '2000-06-07' union all
select '003', '1980-10-11' union all
select '004', '1982-08-25'
select * from userinfoselect userid,bir from userinfo where datepart(d,bir)-datepart(d,getdate())>=-7 and datepart(d,bir)-datepart(d,getdate())<=7userid bir
---------- ------------------------------------------------------
004 1982-08-25 00:00:00.000(所影响的行数为 1 行)
when month(getdate()) in(2,4,6,9,11 )then 30*month(getdate())
else month(getdate())*28
end )-(case when month(bir) in (1,3,5,7,8,10,12) then 31*month(bir)
when month(bir) in(2,4,6,9,11 )then 30*month(bir)
else month(bir)*28
end )as days,* from userinfo) t where abs(days)<7
where dateadd(year,datediff(year,bir,dateadd(day,-40,getdate())),bir)
between dateadd(day,-40,getdate()) and dateadd(day,40,getdate())
or
dateadd(year,datediff(year,bir,dateadd(day,40,getdate())),bir)
between dateadd(day,-40,getdate()) and dateadd(day,40,getdate())
(
userid varchar(10),
bir datetime
)
insert userinfo
select '001', '1990-01-01' union all
select '002', '2000-06-07' union all
select '003', '1980-10-11' union all
select '004', '1982-08-25'go
create function getdays(@start datetime,@end datetime)
returns int
as
begin
declare @month int,@days int,@startdays int,@enddays int
select @month=month(@start),@days=day(@start),@startdays=0
while (@month>0)
begin
if (@month in (1,3,5,7,8,10,12))
set @startdays=@startdays+31
else if(@month in (2,4,6,9,11))
set @startdays=@startdays+30
else
set @startdays=@startdays+28
set @month=@month-1
end
select @startdays=@startdays+@days
select @month=month(@end),@days=day(@end),@enddays=0
while (@month>0)
begin
if (@month in (1,3,5,7,8,10,12))
set @enddays=@enddays+31
else if(@month in (2,4,6,9,11))
set @enddays=@enddays+30
else
set @enddays=@enddays+28
set @month=@month-1
end
select @enddays=@enddays+@days
return abs(@startdays-@enddays)
endgoselect * from userinfo where dbo.getdays(getdate(),bir)<7写了个函数,还是没有判断闰年.我想如果精度不是要求很高的话,应该差不多了.也没有时间精确测试.试试看.
我的操作方法是: 添加一个日期字段,值的计算方法为: 把用户的生日进行转换,转换的方法是:年改为目前的年份,月和日期不变
这样就可以使用
select * from userinfo where bir1 >dateadd(-7,getdate()) bir1为新添加的字段写一个转换过程,每年的1月1日0 点0分0秒执行
select * from userinfo where bir1 between dateadd(d,-7,getdate()) and getdate() bir1为新添加的字段写一个转换过程,每年的1月1日0 点0分0秒执行
caoaibing(caoaibing) ( ) 跟这个是一样的
select * from userinfo
where dateadd(year,datediff(year,bir,dateadd(day,-40,getdate())),bir)
between dateadd(day,-40,getdate()) and dateadd(day,40,getdate())
or
dateadd(year,datediff(year,bir,dateadd(day,40,getdate())),bir)
between dateadd(day,-40,getdate()) and dateadd(day,40,getdate())
不知道你有没有考虑夸年度的情况比如今天为2006-12-29 然后正负40天内的
谢谢,在转换的时候,要考虑上一年7天内的日期.这可以在转换的时候注意一下.你的做法,可能会存在性能问题,如果数据量大的话.会引起表扫描.
create table userinfo
(
userid varchar(10),
bir datetime
)
insert userinfo
select '001', '1990-01-01' union all
select '002', '2000-06-07' union all
select '003', '1980-10-11' union all
select '003', '1989-08-22' union all
select '003', '1990-08-27' union all
select '004', '1982-08-25'select * from userinfo
where rtrim(month(bir)) + '-' + rtrim(day(bir))
between rtrim(month(dateadd(dd,-3,getdate()))) + '-' + rtrim(day(dateadd(dd,-3,getdate())))
AND
rtrim(month(dateadd(dd,3,getdate())))+ '-' + rtrim(day(dateadd(dd,3,getdate())))
drop table userinfo
应该可以解决lz的问题
我给你说下思路吧,将生日+上与今天的年差,然后再与今天求日差
select *
from userinfo
where datediff(day,getdate(),dateadd(year,datediff(year,bir,getdate()),bir)) >0
and datediff(day,getdate(),dateadd(year,datediff(year,bir,getdate()),bir)) <=7
----------------------------------------------------------------------
select datediff(year,'1996/2/29',getdate()),
dateadd(year,datediff(year,'1996/2/29',getdate()),'1996/2/29'),
datediff(day,getdate(),dateadd(year,datediff(year,'1996/2/29',getdate()),'1996/2/29'))
我给你说下思路吧,将生日+上与今天的年差,然后再与今天求日差在0到7之间,
或者将生日+上与今天的年差+1年,然后再与今天求日差在0到7之间
是不是应该这么改啊
select *
from userinfo
where datediff(day,getdate(),dateadd(year,datediff(year,bir,getdate()),bir)) between 0 to 7
OR datediff(day,getdate(),dateadd(year,datediff(year,bir,getdate())+1,bir)) between 0 to 7