SQL同时显示月份和年份两个日期之间[复制](SQL to display both month a

2019-10-18 18:28发布

这个问题已经在这里有一个答案:

  • 获取日期范围之间的日期 7个回答

我有四个变量CurrentMonth,CurrentYear,月,年。 我想有它显示所有的几个月和几年(月,年)之间(currentMonth,CurrentYear)的SQL语句。 显示在下面几个月,但我需要个月,一年,我没有日期,但起点和终点的只有月份和年份。

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);

Answer 1:

这是从indicently以前的答案修改,以满足您的月/年的需求。

    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);


文章来源: SQL to display both month and year between two dates [duplicate]