用sql語句如何查找一個日期

[sfanpu]

比如

name birt
--------------------
erick 1999/2/2
winc 1988/2/3
rebert 1999/2/2

如何用sql語句查找birt為“2/2“的資料呢？其中birt類型為datatime

我用下面的語句查了但是不對
select * from table1 where birt like '%2/2%'

謝謝！

[xluzhong]

select * from table1 where convert(char(5),birt,101)='2/2'

[Softlee81307]

create table kk(name varchar(10), birt datetime)
insert into kk
select 'erick' , '1999/2/2' union all
select 'winc' , '1988/2/3' union all
select 'rebert', '1999/2/2'


----------------------------
select * from kk where cast(datepart(mm,birt) as varchar(2))+'/'+cast(datepart(dd,birt) as varchar(2)) ='2/2'

[Softlee81307]

select * from kk where cast(datepart(mm,birt) as varchar(2))+'/'+cast(datepart(dd,birt) as varchar(2)) ='2/2'

[Softlee81307]

create table kk(name varchar(10), birt datetime)
insert into kk
select 'erick' , '1999/2/2' union all
select 'winc' , '1988/2/3' union all
select 'rebert', '1999/2/2'
----------------------------實現語句-----------
select * from kk where cast(datepart(mm,birt) as varchar(2))+'/'+cast(datepart(dd,birt) as varchar(2)) ='2/2'
---------------結果------------------
erick 1999-02-02 00:00:00.000
rebert 1999-02-02 00:00:00.000

[xluzhong]

select * from table1 where convert(char(5),birt,101)='02/02'

[xluzhong]

create table kk(name varchar(10), birt datetime)
insert into kk
select 'erick' , '1999/2/2' union all
select 'winc' , '1988/2/3' union all
select 'rebert', '1999/2/2'
----------------------------實現語句-----------
select * from KK where convert(char(5),birt,101)='02/02'

DROP TABLE KK

[LoveLwn]

select * from table1 where convert(char(5),birt,101)='2/2'

足够了

[remote_peng]

select * from table where month(birt)=2 and day(birt)=2

[sfanpu]

謝謝各位！

[子陌红尘]

select
*
from
table1
where
replace(convert(varchar(10),birt,111),'/0','/') like '%2/2'