How to get month list in SQL Server?

Question

Hello to all,     How to get all month list of a year by a given year with out using loop like below stracture. MID   MNAME 1         Jan-20 2         Feb-20 3         Mar-20 4         Apr-20 5         May-20 6         Jun-20 7         Jul-20 8         Aug-20 9         Sep-20 10       Oct-20 11       Nov-20 12       Dec-20

Joginder Banger · Accepted Answer

DECLARE  @ Date   DATE  =  '2020-03-01' ,           @inc  INT  = 0   ; with  cte  as    (        select         @inc  AS  Inc,       DATENAME(mm,@ Date )  AS  [MonthName],       DATEPART(mm,@ Date )  AS  [MonthNumber],       DATEPART(yy,@ Date )  as  [MonthYear]        UNION   ALL         select         inc+1,        DATENAME(mm,DATEADD(mm,inc+1,@ Date )),        DATEPART(mm,DATEADD(mm,inc+1,@ Date )),        DATEPART(yy, DATEADD(mm,inc+1,@ Date ))         FROM  cte        where  inc  )   select  [MonthName],[MonthNumber],[MonthYear]  from  cte    As per your requirement i shared some similar code, please try and let me know if any thing miss my side.

Amit Mohanty · Answer

Hey, try this
DECLARE  @ YEAR  NVARCHAR(4) =  '2020'           SELECT  MONTHID, CONVERT ( CHAR (3), DATENAME( MONTH , DATEADD( MONTH  , MONTHID , -1 ))) +  ' - '  +  RIGHT (@ YEAR , 2) [MONTHNAME]  FROM    (          SELECT    MONTH (DATEADD( MONTH , X.NUMBER, @ YEAR  +  '-01-01' ))  AS  MONTHID        FROM     MASTER.DBO.SPT_VALUES X          WHERE    X.TYPE =  'P'                   AND      X.NUMBER  MONTH , @ YEAR  +  '-01-01' ,  @ YEAR  +  '-12-31' )     ) A