Get All Dates of Given Month and Year in SQL Server

Question

I want to get all dates by declaring month and year in SQL server.

Can anyone please share easy lines of SQL code to get it.

For example:

DECLARE @month AS INT = 5
DECLARE @Year AS INT = 2016
SELECT * from Something

I have tried below things,

DECLARE @month TINYINT=5

;WITH CTE_Days AS (
    SELECT DATEADD(
               MONTH,
               @month,
               DATEADD(
                   MONTH,
                   -MONTH(GETDATE()),
                   DATEADD(
                       DAY,
                       -DAY(GETDATE()) + 1,
                       CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)
                   )
               )
           ) Dates
    UNION ALL
    SELECT DATEADD(DAY, 1, Dates)
    FROM   CTE_Days
    WHERE  Dates < DATEADD(
               DAY,
               -1,
               DATEADD(
                   MONTH,
                   1,
                   DATEADD(
                       MONTH,
                       @month,
                       DATEADD(
                           MONTH,
                           -MONTH(GETDATE()),
                           DATEADD(
                               DAY,
                               -DAY(GETDATE()) + 1,
                               CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)
                           )
                       )
                   )
               )
           )
)
SELECT Dates
FROM   CTE_Days

But I am looking for easy solution with less lines and short answer

Answer 1

Same approach as t-clausen, but a more compact:

Declare @year int = 2017, @month int = 11;
WITH numbers
as
(
    Select 1 as value
    UNion ALL
    Select value + 1 from numbers
    where value + 1 <= Day(EOMONTH(datefromparts(@year,@month,1)))
)
SELECT datefromparts(@year,@month,numbers.value) Datum FROM numbers