想做一个会员生日提醒功能,要查出最近10天过生日的会员。表: base_user(会员)
字段: uid(会员id) / birthday(生日)
数据库:sqlserver2000
字段: uid(会员id) / birthday(生日)
数据库:sqlserver2000
解决方案 »
- 高手請進,爲什麽翻來覆去的計算就是不對呢
- 大家来讨论啊,自己答不上来的看看别人怎么回答,最好的五个答案平分100分。
- SQL2005新建用户出错了!!!
- 求2条SQL语句
- --求一数据库备份方案!--
- 新年快乐!请教一个问题:如何将日期型转为字符型?
- 连上库后,什么SQL语句可知道库里有什么信息,包括表(请最快答复)
- 关于密码检测的SQL语句,,个人认为比较难
- 在VB中怎样用ADO 从SQL Server 7.0中导出表到FoxPro、Access、Excel中
- 求助,在线等!!!用错误的bak文件恢复了数据库,能返回到恢复之前的状态吗?
- 初学sql,这个insert应该如何写?
- 求助:ACCESS数据库转成sql 2000 就出问题了........
where datediff(dd,getdate(),birthday)<10
where datediff(dd,getdate(),birthday) between 0 and 10 end
select uid from base_user
where datediff(dd,getdate(),birthday) between 0 and 10
from base_user
where birthday between getdate() and dateadd(day,10,getdate()) end
from base_user
where datediff(day,@current_date,dateadd(year,datediff(year,birthday,@current_date)+case when dateadd(year,datediff(year,birthday,@current_date),birthday) < @current_date then 1 else 0 end,birthday))<10
select *
from base_user
where datediff(day,getdate(),dateadd(year,datediff(year,birthday,getdate())+case when dateadd(year,datediff(year,birthday,getdate()),birthday) < getdate() then 1 else 0 end,birthday))<10
select * from base_user where datediff(day,cast(left(convert(varchar(10),getdate(),120),4) + right(convert(varchar(10),birthday,120),6) as datetime),getdate())<=10
--指今天之后的十天
select * from base_user where datediff(day,getdate(),cast(left(convert(varchar(10),getdate(),120),4) + right(convert(varchar(10),birthday,120),6) as datetime))<=10
--指今天之前之后的各十天
select * from base_user where abs(datediff(day,getdate(),cast(left(convert(varchar(10),getdate(),120),4) + right(convert(varchar(10),birthday,120),6) as datetime)))<=10
--------------------
select * from base_user
where datediff(d,getdate(),dateadd(yy,datediff(yy,dt,getdate()),dt)) between 0 and 10 and datepart(d,dt)=datepart(d,dateadd(yy,datediff(yy,dt,getdate()),dt))
思路是:把生日的年份转为当前日期的年份,然后比较两个日期。假设今天是2007-12-31,会员生日是1974-1-5,还有5天会员过生日,这个查询方法好像实现不了,我说的对吗?
select uid
from base_user
where datediff(d,cast(left(Convert(varchar(8),getdate(),112),4)+right(Convert(varchar(8),birthday,112),4) as datetime),getdate())
between 0 and 10
Test Case:
select 1
where
datediff(d,cast(left(Convert(varchar(8),getdate(),112),4)+right(Convert(varchar(8),'1997-5-5',112),4) as datetime),getdate())
between 0 and 10
My answer:
select uid
from base_user
where datediff(d,cast(left(Convert(varchar(8),getdate(),112),4)+right(Convert(varchar(8),birthday,112),4) as datetime),getdate())
between 0 and 10
看看这个吧,我先计算了每个人的下一个"生日":NextBD,然后与当前日期比较,最后比较NextBD与birthday的datepart(day,birthday)排除2月29日不存在的情况,经过测试,可行,呵呵~!~!
*/
select * from
(select *,NextBD=case when dateadd(yy,datediff(yy,birthday,getdate()),birthday)>getdate() then dateadd(yy,datediff(yy,birthday,getdate()),birthday) else dateadd(yy,datediff(yy,birthday,getdate())+1,birthday) end from base_user) as tb
where datediff(d,getdate(),NextBD) between 0 and 10 and datepart(d,birthday)=datepart(d,NextBD)