select * from tb where convert(varchar(7),时间字段,120) between '2007-01' and '2007-06'
declare @datebegin varchar(10),@dateend varchar(10),@begin datetime,@end datetime select @datebegin='200701',@dateend='200706' select @begin=cast(@datebegin+'01' as datetime),@end=cast(@dateend+'01' as datetime) while @begin<=@end begin print convert(char(6),@begin,112) set @begin=dateadd(month,1,@begin) end /* 200701 200702 200703 200704 200705 200706 */
WITH L0 AS(SELECT 1 AS c UNION ALL SELECT 1), L1 AS(SELECT 1 AS c FROM L0 AS A, L0 AS B), L2 AS(SELECT 1 AS c FROM L1 AS A, L1 AS B), L3 AS(SELECT 1 AS c FROM L2 AS A, L2 AS B), L4 AS(SELECT 1 AS c FROM L3 AS A, L3 AS B), L5 AS(SELECT 1 AS c FROM L4 AS A, L4 AS B), Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS n FROM L5) SELECT dateadd(month,n-1,cast('20070101' as datetime)) FROM Nums WHERE n <= datediff(month,cast('20070101' as datetime),cast('20070601' as datetime))+1
select @datebegin='200701',@dateend='200706'
select @begin=cast(@datebegin+'01' as datetime),@end=cast(@dateend+'01' as datetime)
while @begin<=@end
begin
print convert(char(6),@begin,112)
set @begin=dateadd(month,1,@begin)
end
/*
200701
200702
200703
200704
200705
200706
*/
L0 AS(SELECT 1 AS c UNION ALL SELECT 1),
L1 AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
L2 AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
L3 AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
L4 AS(SELECT 1 AS c FROM L3 AS A, L3 AS B),
L5 AS(SELECT 1 AS c FROM L4 AS A, L4 AS B),
Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS n FROM L5)
SELECT dateadd(month,n-1,cast('20070101' as datetime)) FROM Nums
WHERE n <= datediff(month,cast('20070101' as datetime),cast('20070601' as datetime))+1
傳到另一個SQL里面