select * from tb where convert(varchar(10),日期,120) < convert(varchar(10),getdate(),120)
每当传递参数,YEAR,MONTH?? 是按这两参数还是当前日期?
declare @year varchar(4) , @month varchar(4)select @year='2007',@month='12'select * from 表table where DATEDIFF(day,[Datetime] ,cast(@year+'-'+@month+'-'+'01' as datetime)) >=0
----examplecreate table tab(ID int,Name varchar(10),code varchar(10),[Datetime] datetime) insert tabselect 1,'aa','aaa','2006-01-05' union select 2,'bb','bbb','2007-10-12' union select 2,'bb','bbb','2007-11-12' union select 2,'bb','bbb','2007-12-12' union select 2,'bb','bbb','2008-12-12'declare @year varchar(4) , @month varchar(4)select @year='2007',@month='12'select * from tab where DATEDIFF(month,[Datetime] ,cast(@year+'-'+@month+'-'+'01' as datetime)) >=0drop table tab/* 结果 ID Name code Datetime ----------- ---------- ---------- ------------------------------------------------------ 1 aa aaa 2006-01-05 00:00:00.000 2 bb bbb 2007-10-12 00:00:00.000 2 bb bbb 2007-11-12 00:00:00.000 2 bb bbb 2007-12-12 00:00:00.000(所影响的行数为 4 行) */
非常感谢大家帮忙。。要统计’200712‘之前的记录就如下了:declare @year varchar(4) , @month varchar(4) select @year='2007',@month='12' select count(1) as total from tab where DATEDIFF(month,[Datetime] ,cast(@year+'-'+@month+'-'+'01' as datetime)) >=0
select logouttime_ from Currentuser where Convert(varchar(10),logouttime_)=Convert(varchar(10),getdate())
是按这两参数还是当前日期?
insert tabselect 1,'aa','aaa','2006-01-05'
union select 2,'bb','bbb','2007-10-12'
union select 2,'bb','bbb','2007-11-12'
union select 2,'bb','bbb','2007-12-12'
union select 2,'bb','bbb','2008-12-12'declare @year varchar(4) , @month varchar(4)select @year='2007',@month='12'select * from tab where DATEDIFF(month,[Datetime] ,cast(@year+'-'+@month+'-'+'01' as datetime)) >=0drop table tab/* 结果
ID Name code Datetime
----------- ---------- ---------- ------------------------------------------------------
1 aa aaa 2006-01-05 00:00:00.000
2 bb bbb 2007-10-12 00:00:00.000
2 bb bbb 2007-11-12 00:00:00.000
2 bb bbb 2007-12-12 00:00:00.000(所影响的行数为 4 行)
*/
select @year='2007',@month='12'
select count(1) as total from tab where DATEDIFF(month,[Datetime] ,cast(@year+'-'+@month+'-'+'01' as datetime)) >=0
select logouttime_ from Currentuser where Convert(varchar(10),logouttime_)=Convert(varchar(10),getdate())