34,593
社区成员
发帖
与我相关
我的任务
分享
select name, riqi from T1
where month(riqi)*100+day(riqi) < month(getdate())*100+day(getdate())
create table tb(name varchar(10),riqi datetime)
insert into tb
select '111' ,'2010-12-31' union all
select '222' ,'2010-03-31' union all
select '333' ,'2010-02-10' union all
select '444' ,'2011-03-31' union all
select '555' ,'2011-12-31'
go
select *
from tb
where substring(convert(varchar(8),riqi,112),5,4) = substring(convert(varchar(8),dateadd(dd,-1,cast('2012-1-1' as datetime)),112),5,4)
drop table tb
/*
name riqi
---------- -----------------------
111 2010-12-31 00:00:00.000
555 2011-12-31 00:00:00.000
(2 行受影响)
-- Test Data: ta
If object_id('ta') is not null
Drop table ta
Go
Create table ta(name int,riqi smalldatetime)
Go
Insert into ta
select 111,'2010-12-31' union all
select 222,'2010-03-25' union all
select 333,'2010-02-10' union all
select 444,'2010-04-02' union all
select 555,'2011-04-02'
Go
--Start
Select *
from ta
where right(convert( char(10),cast('2011-01-01' as datetime)-1,120) ,5) = right(convert( char(10),riqi,120) ,5)
--Result:
/*
name riqi
----------- ------------------------------------------------------
111 2010-12-31 00:00:00
(所影响的行数为 1 行)
*/
--End
create table tb(name varchar(10),riqi datetime)
insert into tb
select '111' ,'2010-04-20' union all
select '222' ,'2010-03-31' union all
select '333' ,'2010-02-10' union all
select '444' ,'2010-03-31' union all
select '555' ,'2011-04-02'
go
select *
from tb
where substring(convert(varchar(8),riqi,112),5,4) = substring(convert(varchar(8),dateadd(dd,-1,cast('2011-4-1' as datetime)),112),5,4)
drop table tb
/*
name riqi
---------- -----------------------
222 2010-03-31 00:00:00.000
444 2010-03-31 00:00:00.000
(2 行受影响)
select * from tb
where month(riqi)=month(dateadd(dd,-1,getdate()))
and datepart(day,riqi)=datepart(dateadd(dd,-1,getdate()))
create table tb(name varchar(10),riqi datetime)
insert into tb
select '111' ,'2010-04-20' union all
select '222' ,'2010-03-25' union all
select '333' ,'2010-02-10' union all
select '444' ,'2010-04-02' union all
select '555' ,'2011-04-02'
go
select *
from tb
where substring(convert(varchar(8),riqi,112),5,4) = substring(convert(varchar(8),dateadd(dd,-1,getdate()),112),5,4)
drop table tb
/*
name riqi
---------- -----------------------
444 2010-04-02 00:00:00.000
555 2011-04-02 00:00:00.000
(2 行受影响)