問(wèn)題描述

我有四個(gè)變量 CurrentMonth、CurrentYear、Month、year.我想要一個(gè) SQL 語(yǔ)句，它顯示 (Month,Year) 到 (currentMonth,CurrentYear) 之間的所有月份和年份.下面顯示月份，但我需要月份和年份，我沒(méi)有日期，但只有開(kāi)始和結(jié)束的月份和年份.

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

推薦答案

這是對(duì)上一個(gè)回答的修改，來(lái)自 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);

這篇關(guān)于SQL 顯示兩個(gè)日期之間的月份和年份的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！