取所有月份:
declare @begin datetime,@end datetime
set @begin='2015-2-6'
set @end='2015-12-2'
declare @months int
set @months=DATEDIFF(month,@begin,@end)
select convert(varchar(7),DATEADD(month,number,@begin) ,120) AS 月份
from master.dbo.spt_values
where type='p' AND number<=@months
取所有日期
DECLARE @days INT,
@date_start DATETIME = '2017-03-15',
@date_end DATETIME = '2017-04-13'
SET @days = DATEDIFF(DAY, @DATE_START, @DATE_END);
SELECT convert(char(10), DATEADD(dd, number, @DATE_START),120) AS yyyymmdd
FROM master.dbo.spt_values as spt
WHERE type = 'p'
AND number <= @days
然后可以再进行一些left join操作和某个表关联得到某个表某段时间没出现过的日期。一个小功能要用的到记录一下。