这是保存用户的出生日期字段。。
如何查询出今天生日的用户呢?
求解。还有闰年平年的判断。2月份29日生日的就当作28号生日。
10 个解决方案
#1
select * from tb where CONVERT(varchar(5),Birthdate,110)=CONVERT(varchar(5),GETDATE(),110)
#2
select name from tb where
convert(varchar(10),birthdate,120) = convert(varchar(10),getdate(),120)
#3
select * from tb
where month(Birthdate)=month(GETDATE()) and day(Birthdate)=day(GETDATE())
#4
declare @t table (name varchar(20),date datetime)
insert into @t
select 'a','1989-02-01' union all
select 'b','1982-02-28' union all
select 'c','1996-02-29' union all
select 'd','2000-02-29' union all
select 'e','1978-02-22' union all
select 'f','1978-09-29' union all
select 'g','1976-09-29'
select name from @t where
convert(varchar(5),date,110)=convert(varchar(5),getdate(),110)
/*
name
--------------------
f
g
*/
--如果29的生日,不是闰年的推到28号,case when 一下。
#5
考虑平年闰年:
select * from tb
where CONVERT(varchar(5),(case when convert(varchar(5),DATEADD(d,1,Birthdate),110)='03-01' then dateadd(d,-1,ltrim(YEAR(getdate()))+'-03-01') else Birthdate end),110)=CONVERT(varchar(5),GETDATE(),110)
#6
我擦,又错了
#7
为何我看问题总是往最简单的上面想呢
#8
--换个思路写一个
select name from @t
where convert(varchar(5),date,110)=convert(varchar(5),getdate(),110) --这个是当天生日的
union all
select name from @t
where convert(varchar(5),date,110)='02-29' --这个是2月29号生日的
and convert(varchar(5),getdate(),110)='02-28' --当天28号
and month(getdate()+1)=3 --不是闰年的情况
#9
create table tb(name varchar(20),birthdate datetime)
insert into tb
select 'a','1989-02-01' union all
select 'b','1983-02-28' union all
select 'c','1996-02-29' union all
select 'd','2000-02-29' union all
select 'e','1978-02-22' union all
select 'f','1978-09-29' union all
select 'g','1976-09-29'
go
declare @today datetime
set @today='2013-02-28'
select * from tb where (case when convert(varchar(5),DATEADD(d,1,birthdate),110)='03-01' then DATEADD(D,-1,ltrim(year(@today))+'-03-01') else ltrim(YEAR(@today))+'-'+CONVERT(varchar(5),birthdate,110) end)=@today
/*
name birthdate
-------------------- -----------------------
b 1983-02-28 00:00:00.000
c 1996-02-29 00:00:00.000
d 2000-02-29 00:00:00.000
(3 行受影响)
*/
go
drop table tb
#10
判断是否闰年:
SELECT case day(dateadd(mm, 2, dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))))
when 28 then '平年' else '闰年' end
#1
select * from tb where CONVERT(varchar(5),Birthdate,110)=CONVERT(varchar(5),GETDATE(),110)
#2
select name from tb where
convert(varchar(10),birthdate,120) = convert(varchar(10),getdate(),120)
#3
select * from tb
where month(Birthdate)=month(GETDATE()) and day(Birthdate)=day(GETDATE())
#4
declare @t table (name varchar(20),date datetime)
insert into @t
select 'a','1989-02-01' union all
select 'b','1982-02-28' union all
select 'c','1996-02-29' union all
select 'd','2000-02-29' union all
select 'e','1978-02-22' union all
select 'f','1978-09-29' union all
select 'g','1976-09-29'
select name from @t where
convert(varchar(5),date,110)=convert(varchar(5),getdate(),110)
/*
name
--------------------
f
g
*/
--如果29的生日,不是闰年的推到28号,case when 一下。
#5
考虑平年闰年:
select * from tb
where CONVERT(varchar(5),(case when convert(varchar(5),DATEADD(d,1,Birthdate),110)='03-01' then dateadd(d,-1,ltrim(YEAR(getdate()))+'-03-01') else Birthdate end),110)=CONVERT(varchar(5),GETDATE(),110)
#6
我擦,又错了
#7
为何我看问题总是往最简单的上面想呢
#8
--换个思路写一个
select name from @t
where convert(varchar(5),date,110)=convert(varchar(5),getdate(),110) --这个是当天生日的
union all
select name from @t
where convert(varchar(5),date,110)='02-29' --这个是2月29号生日的
and convert(varchar(5),getdate(),110)='02-28' --当天28号
and month(getdate()+1)=3 --不是闰年的情况
#9
create table tb(name varchar(20),birthdate datetime)
insert into tb
select 'a','1989-02-01' union all
select 'b','1983-02-28' union all
select 'c','1996-02-29' union all
select 'd','2000-02-29' union all
select 'e','1978-02-22' union all
select 'f','1978-09-29' union all
select 'g','1976-09-29'
go
declare @today datetime
set @today='2013-02-28'
select * from tb where (case when convert(varchar(5),DATEADD(d,1,birthdate),110)='03-01' then DATEADD(D,-1,ltrim(year(@today))+'-03-01') else ltrim(YEAR(@today))+'-'+CONVERT(varchar(5),birthdate,110) end)=@today
/*
name birthdate
-------------------- -----------------------
b 1983-02-28 00:00:00.000
c 1996-02-29 00:00:00.000
d 2000-02-29 00:00:00.000
(3 行受影响)
*/
go
drop table tb
#10
判断是否闰年:
SELECT case day(dateadd(mm, 2, dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))))
when 28 then '平年' else '闰年' end