declare @s datetime set @s='2012-07-01' declare @e datetime set @e='2013-07-01' select CONVERT(varchar(7),DATEADD(mm,number,@s),120) from master..spt_values where type='p' and number between 0 and DATEDIFF(MM,@s,@e) /* (无列名) 2012-07 2012-08 2012-09 2012-10 2012-11 2012-12 2013-01 2013-02 2013-03 2013-04 2013-05 2013-06 2013-07 */
DECLARE @a VARCHAR(10),@b VARCHAR(10) SELECT @a='2012-07',@b='2013-07'SELECT CONVERT(VARCHAR(7),DATEADD(mm,number,@a+'-01'),120) FROM master..spt_values WHERE type='p' AND number BETWEEN 0 AND DATEDIFF(mm,@a+'-01',@b+'-01')
declare @dt1 datetime set @dt1='2012-07-01' declare @dt2 datetime set @dt2='2013-07-01' ;WITH dt AS (SELECT @dt1 AS dt UNION ALL SELECT dt=DATEADD(mm,1,dt) FROM dt WHERE dt<@dt2 ) SELECT CONVERT(VARCHAR(7),dt,120) AS dt FROM dt /* 2012-07 2012-08 2012-09 2012-10 2012-11 2012-12 2013-01 2013-02 2013-03 2013-04 2013-05 2013-06 2013-07*/
declare @s datetime
set @s='2012-07-01'
declare @e datetime
set @e='2013-07-01'
select CONVERT(varchar(7),DATEADD(mm,number,@s),120)
from master..spt_values
where type='p' and number between 0 and DATEDIFF(MM,@s,@e)
/*
(无列名)
2012-07
2012-08
2012-09
2012-10
2012-11
2012-12
2013-01
2013-02
2013-03
2013-04
2013-05
2013-06
2013-07
*/
SELECT @a='2012-07',@b='2013-07'SELECT CONVERT(VARCHAR(7),DATEADD(mm,number,@a+'-01'),120)
FROM master..spt_values
WHERE type='p'
AND number BETWEEN 0 AND DATEDIFF(mm,@a+'-01',@b+'-01')