SqlServer 获取时间列表

按日期获取日列表

select 
	DATEADD(DAY,number,CONVERT(datetime,'9/1/2017',101)) ddate
	from master..spt_values
	where type='p' AND
number<=DATEDIFF(day,CONVERT(datetime,'9/1/2017',101),CONVERT(datetime,'12/22/2020',101))

获取本月已过天数及上月的日列表

select
	DATEADD(DAY,number,CONVERT(datetime,DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0),101)) ddate
	from master..spt_values
	where type='p' AND
	number<=DATEDIFF(day,CONVERT(datetime,DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0),101),CONVERT(datetime,GETDATE()-1,101))

按日期获取月列表

select convert(varchar(7),dateadd(month,number,'2017-09-01'),120)
from master..spt_values
where type='P' and dateadd(month,number,'2017-09-01')<='2020-12-01'

获取本月及过去9个月的月列表

select convert(varchar(7),dateadd(month,number,CONVERT(datetime,DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-9, 0),101)),120)
from master..spt_values
where type='P' and dateadd(month,number,CONVERT(datetime,DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-9, 0),101))<=CONVERT(datetime,GETDATE()-1,101)