declare @dt as varchar(10) set @dt = '2006' / '2006-10' / '2006-11-07' convert(varchar(10) , yourdatetime , 120) like @dt
select * from tablename where convert(char(8), col, 112) like '2006%' select * from tablename where convert(char(8), col, 112) like '200610%' select * from tablename where convert(char(8), col, 112) like '20061107%'select convert(char(8), getdate(), 112)
declare @dt as varchar(10) set @dt = '2006' + '%' ('2006-10' + '%' or '2006-11-07' + '%') convert(varchar(10) , yourdatetime , 120) like @dt
if @tempday!=0 AND @tempmonth!=0 AND @tempyear!=0 select * from ViewLog where year(ViewTime)=@tempyear and month(ViewTime)=@tempmonth and day(ViewTime) = @tempday if @tempyear!=0 AND @tempmonth =0 AND @tempyear!=0 return @err if @tempyear!=0 AND @tempmonth !=0 AND @tempyear=0 select * from ViewLog where year(ViewTime)=@tempyear AND month(ViewTime)=@tempmonth if @tempyear=0 AND @tempmonth !=0 AND @tempyear!=0 select * from ViewLog where year(ViewTime)=year(getdate()) AND month(ViewTime)=@tempmonth and day(ViewTime) = @tempday if @tempyear=0 AND @tempmonth =0 AND @tempyear!=0 select * from ViewLog where year(ViewTime)=year(getdate()) AND month(ViewTime)=month(getdate()) and day(ViewTime) = @tempday if @tempyear=0 AND @tempmonth !=0 AND @tempyear=0 select * from ViewLog where year(ViewTime)=year(getdate()) AND month(ViewTime)=@tempmonth if @tempyear!=0 AND @tempmonth =0 AND @tempyear=0 select * from ViewLog where year(ViewTime)=@tempyear 这是我写的存储过程,有点太笨了,想找个方便的方法 上面是一个一个进行判断,,,还不能满足一个时间段的查询,谁有好点的方法啊
上面就是根据用户输入的参数(年月日)进行select查询,只不过用了很多if.
marco08()你的方法可行,那我想查一段时间内的数据,你再补充一下了,谢谢了
一段时间内的数据要用between ... and 如果輸入2006, 則 between '2006-1-1' and '2006-12-31' 如果輸入200607 則 between '2006-07-01' and '2006-07-31'
set @dt = '2006' / '2006-10' / '2006-11-07'
convert(varchar(10) , yourdatetime , 120) like @dt
select * from tablename where convert(char(8), col, 112) like '200610%'
select * from tablename where convert(char(8), col, 112) like '20061107%'select convert(char(8), getdate(), 112)
set @dt = '2006' + '%' ('2006-10' + '%' or '2006-11-07' + '%')
convert(varchar(10) , yourdatetime , 120) like @dt
select * from ViewLog where year(ViewTime)=@tempyear and month(ViewTime)=@tempmonth and day(ViewTime) = @tempday if @tempyear!=0 AND @tempmonth =0 AND @tempyear!=0
return @err if @tempyear!=0 AND @tempmonth !=0 AND @tempyear=0
select * from ViewLog where year(ViewTime)=@tempyear AND month(ViewTime)=@tempmonth if @tempyear=0 AND @tempmonth !=0 AND @tempyear!=0
select * from ViewLog where year(ViewTime)=year(getdate()) AND month(ViewTime)=@tempmonth and day(ViewTime) = @tempday if @tempyear=0 AND @tempmonth =0 AND @tempyear!=0
select * from ViewLog where year(ViewTime)=year(getdate()) AND month(ViewTime)=month(getdate()) and day(ViewTime) = @tempday if @tempyear=0 AND @tempmonth !=0 AND @tempyear=0
select * from ViewLog where year(ViewTime)=year(getdate()) AND month(ViewTime)=@tempmonth if @tempyear!=0 AND @tempmonth =0 AND @tempyear=0
select * from ViewLog where year(ViewTime)=@tempyear
这是我写的存储过程,有点太笨了,想找个方便的方法
上面是一个一个进行判断,,,还不能满足一个时间段的查询,谁有好点的方法啊
如果輸入2006, 則 between '2006-1-1' and '2006-12-31'
如果輸入200607 則 between '2006-07-01' and '2006-07-31'