select substring(convert(varchar,dateadd(m,number,'2016-01-01'),112),1,6)
from master..spt_values
where type = 'p' and dateadd(m,number,'2016-01-01') >= '2016-01-01'
and dateadd(m,number,'2016-01-01') <= '2016-02-01'
其中用到了取数表
master..spt_values
select substring(convert(varchar,dateadd(m,number,'2016-01-01'),112),1,6)
from master..spt_values
where type = 'p' and dateadd(m,number,'2016-01-01') >= '2016-01-01'
and dateadd(m,number,'2016-01-01') <= '2016-02-01'
master..spt_values