问题描述
我有四个变量 CurrentMonth、CurrentYear、Month、year.我想要一个 SQL 语句,它显示 (Month,Year) 到 (currentMonth,CurrentYear) 之间的所有月份和年份.下面显示月份,但我需要月份和年份,我没有日期,但只有开始和结束的月份和年份.
I have four variables CurrentMonth, CurrentYear, Month, year. I want to have a SQL statement which displays all the months and years between (Month,Year) to (currentMonth,CurrentYear). The below displays months but I need months and year and I dont have dates but only month and year of start and End.
DECLARE @StartDate DATETIME, @EndDate DATETIME; SELECT @StartDate = '20110501' ,@EndDate = '20110801'; SELECT DATENAME(MONTH, DATEADD(MONTH, x.number, @StartDate)) AS MonthName FROM master.dbo.spt_values x WHERE x.type = 'P' AND x.number <= DATEDIFF(MONTH, @StartDate, @EndDate);
推荐答案
这是对上一个回答的修改,来自 indicently 以满足您的月/年需求.
This is a modification from the previous answer from indicently to meet your Month/Year needs.
DECLARE @FromMonth varchar(2), @FromYear varchar(4), @ToMonth as Varchar(2), @ToYear as varchar(4) SET @FromMonth = '01'; SET @FromYear = '1944'; SET @ToMonth = '04'; SET @ToYear = '1956'; DECLARE @DateFrom smalldatetime, @DateTo smalldatetime; SET @DateFrom=@FromYear+@FromMonth+'01'; SET @DateTo=@ToYear+@ToMonth+'01'; ------------------------------- WITH T(date) AS ( SELECT @DateFrom UNION ALL SELECT DateAdd(month,1,T.date) FROM T WHERE T.date < @DateTo ) SELECT datename(m,date) + '-' + datename(yy,date) as DN FROM T OPTION (MAXRECURSION 32767);