日期SQl
table name:tb
ID date(varchar)
1 200701
2 200702
我现在查询我输入的月份前6个月
如
输入200707
查询200707-200702
不考虑连续的问题,就是中间有空的就不查
------解决方案--------------------declare @a table(ID int, date varchar(20))
insert @a select 1 , '200701 '
union all select 2 , '200702 '
declare @d varchar(10)
set @d= '200707 '
select * from @a where date between convert(varchar(6),dateadd(month,-5,cast(@d+ '01 ' as smalldatetime)),112) and @d
--result
/*
ID date
----------- --------------------
2 200702
(所影响的行数为 1 行)
*/
------解决方案--------------------create table tb(ID int,Tdate varchar(6))
insert tb
select 1, '200701 ' union all
select 2, '200702 ' union all
select 3, '200704 ' union all
select 4, '200704 ' union all
select 5, '200706 ' union all
select 6, '200705 ' union all
select 7, '200706 ' union all
select 8, '200707 ' union all
select 9, '200708 '
declare @start varchar(6)
declare @end varchar(6)
set @end= '200707 '
select @start=convert(varchar(6),dateadd(month,-5,cast (left(@end,4)+ '- '+right(@end,2)+ '-01 ' as datetime)),112)
select * from tb where Tdate> =@start and Tdate <@end
drop table tb