How to get each day number of a month in SQL?-CodePudding

Is there a way to get a select with each day number of a month? For example I have this date: 2021-12-15 and I want to get this result:

| Days| 
|--------| 
|  1|
|  2|
|  3|
|  4|
|  5|

.. 31

CodePudding user response：

You can use a simple recursive CTE to generate a list of numbers and eomonth() for the days in the given date:

declare @date date='20211215';

with d as (
    select 1 num
    union all
    select d.num   1
    from d
    where d.num <= 31
)
select num as [Days]
from d
where num<=Day(EOMonth(@date));