select * from table where datediff(d, getdate(), dateadd(year, datediff(year, birthday, getdate()), birthday)) between 0 and 15
平平淡淡兄: select * from 表名 where datediff(day,getdate(),日期字段)<=15 and datename(month,日期字段)=month(getdate())
啊,搞错,改:select * from 表 where datediff(date,dateadd(year,1900-year(getdate()),getdate()),dateadd(year,1900-year(出生日期字段),出生日期字段)) between 1 and 15
谢谢dlpseeyou(豆子) zjcxc(邹建) !好像还是不行 :(
select * from 表 where (datepart(month,getdate())<>12 and datediff ( day ,getdate() ,dateadd(year,datediff(year,出生日期字段,getdate()),出生日期字段) ) between 0 and 15) or (datepart(month,getdate())=12 and datediff ( day ,getdate() ,dateadd(year,datediff(year,出生日期字段,getdate())-1,出生日期字段) ) between 0 and 15)
--写错了一个参数,这个可以了 select * from 表 where datediff(day ,dateadd(year,1900-year(getdate()),getdate()) ,dateadd(year,1900-year(dt),dt) ) between 1 and 15
--下面是数据测试--测试数据 declare @t table(name varchar(10),dt datetime) insert into @t select 'a','2001-11-11' union all select 'b','2002-12-01' union all select 'd','2003-12-02' union all select 'd','2004-12-03' union all select 'e','1997-12-04' union all select 'f','1977-12-05' union all select 'g','1978-12-06' union all select 'h','1979-12-11' union all select 'i','1980-12-12' union all select 'j','1997-12-13' union all select 'k','1996-12-14' union all select 'l','1995-12-15' union all select 'm','1994-12-16' union all select 'n','1993-12-17' union all select 'o','1992-12-18' union all select 'p','1991-12-19'--查询 select * from @t where datediff(day ,dateadd(year,1900-year(getdate()),getdate()) ,dateadd(year,1900-year(dt),dt) ) between 1 and 15/*--测试结果 name dt ---------- ------------------------------------------------------ e 1997-12-04 00:00:00.000 f 1977-12-05 00:00:00.000 g 1978-12-06 00:00:00.000 h 1979-12-11 00:00:00.000 i 1980-12-12 00:00:00.000 j 1997-12-13 00:00:00.000 k 1996-12-14 00:00:00.000 l 1995-12-15 00:00:00.000 m 1994-12-16 00:00:00.000 n 1993-12-17 00:00:00.000 o 1992-12-18 00:00:00.000(所影响的行数为 11 行) --*/
declare @ datetime set @ = '2000-11-27'--getdate() select * ,dateadd(year,datediff(year,日期字段,@),日期字段) from t where datediff(day,@,dateadd(year,year(@) - year(日期字段),日期字段)) between 1 and 15
谢谢各位 问题解决了,用了 CrazyFor(冬眠的鼹鼠)的方法 select * from 表 where (datepart(month,getdate())<>12 and datediff ( day ,getdate() ,dateadd(year,datediff(year,出生日期字段,getdate()),出生日期字段) ) between 0 and 15) or (datepart(month,getdate())=12 and datediff ( day ,getdate() ,dateadd(year,datediff(year,出生日期字段,getdate())+1,出生日期字段) ) between 0 and 15)不知道有没有更好的办法,马上就揭帖
select * from 表 where datediff(date,getdate,出生日期字段) between 0 and 15
where
datediff
(
day
,getdate()
,dateadd(year,datediff(year,出生日期字段,getdate()),出生日期字段)
) between 0 and 15
select * from 表名 where datename(month,日期字段)=month(getdate()) and datediff(day,getdate(),字段字段) between 1 and 15
where datediff(d, getdate(), dateadd(year, datediff(year, birthday, getdate()), birthday)) between 0 and 15
select * from 表名 where datediff(day,getdate(),日期字段)<=15 and datename(month,日期字段)=month(getdate())
where
(datepart(month,getdate())<>12 and
datediff
(
day
,getdate()
,dateadd(year,datediff(year,出生日期字段,getdate()),出生日期字段)
) between 0 and 15)
or
(datepart(month,getdate())=12 and
datediff
(
day
,getdate()
,dateadd(year,datediff(year,出生日期字段,getdate())-1,出生日期字段)
) between 0 and 15)
select * from 表
where datediff(day
,dateadd(year,1900-year(getdate()),getdate())
,dateadd(year,1900-year(dt),dt)
) between 1 and 15
declare @t table(name varchar(10),dt datetime)
insert into @t
select 'a','2001-11-11'
union all select 'b','2002-12-01'
union all select 'd','2003-12-02'
union all select 'd','2004-12-03'
union all select 'e','1997-12-04'
union all select 'f','1977-12-05'
union all select 'g','1978-12-06'
union all select 'h','1979-12-11'
union all select 'i','1980-12-12'
union all select 'j','1997-12-13'
union all select 'k','1996-12-14'
union all select 'l','1995-12-15'
union all select 'm','1994-12-16'
union all select 'n','1993-12-17'
union all select 'o','1992-12-18'
union all select 'p','1991-12-19'--查询
select * from @t
where datediff(day
,dateadd(year,1900-year(getdate()),getdate())
,dateadd(year,1900-year(dt),dt)
) between 1 and 15/*--测试结果
name dt
---------- ------------------------------------------------------
e 1997-12-04 00:00:00.000
f 1977-12-05 00:00:00.000
g 1978-12-06 00:00:00.000
h 1979-12-11 00:00:00.000
i 1980-12-12 00:00:00.000
j 1997-12-13 00:00:00.000
k 1996-12-14 00:00:00.000
l 1995-12-15 00:00:00.000
m 1994-12-16 00:00:00.000
n 1993-12-17 00:00:00.000
o 1992-12-18 00:00:00.000(所影响的行数为 11 行)
--*/
set @ = '2000-11-27'--getdate()
select * ,dateadd(year,datediff(year,日期字段,@),日期字段)
from t
where datediff(day,@,dateadd(year,year(@) - year(日期字段),日期字段)) between 1 and 15
select * from 表
where
(datepart(month,getdate())<>12 and
datediff
(
day
,getdate()
,dateadd(year,datediff(year,出生日期字段,getdate()),出生日期字段)
) between 0 and 15)
or
(datepart(month,getdate())=12 and
datediff
(
day
,getdate()
,dateadd(year,datediff(year,出生日期字段,getdate())+1,出生日期字段)
) between 0 and 15)不知道有没有更好的办法,马上就揭帖