Count days of month

Question

I was wondering if there is anyway to count the number of days in a month in SQL server 2008... Something similar to this Javascript code I wrote:

 function daysInMonth(month, year) {
                    return new Date(year, month, 0).getDate();
                }

                var dateForApp = new Date();
                var MonthForApp = (dateForApp.getMonth() + 1);
                var yearForApp = dateForApp.getFullYear();

                var daysofMonth = new Array();

                for (var i = 1; i <= daysInMonth(MonthForApp, yearForApp); i++) {
                    daysofMonth.push(parseInt(i));
                } // Resulting in something like this (1,2,3,4,5,6,7,8,9,10,11,12...etc)

I now need to figure out how to do this in SQL... I so far have the foolowing:

declare @Date datetime
select @Date = Getdate()

select datepart(dd,dateadd(dd,-1,dateadd(mm,1,cast(cast(year(@Date) as varchar)+'-'+cast(month(@Date) as varchar)+'-01' as datetime))))

which will let me know how many days there are (31) in the month, but I am now not quite sure how to get the actual counting up done... I was trying while loops etc, but have had no success. Has anyone got any ideas or a thread they could point me to? (I found nothing while searching the net)

Correct me if I am wrong... but that thread tells me how to get how many Days there are in the month? EG: (31) for March... I know how to get this... What I am after is, now that I have my 31, how do I build an Array / Result set of .. 1,2,3,4,5,6,7,8,9,10,11......31 — Fizor, Mar 28 '14 at 14:57
@Bobby, I know this... But for explaining the question I need to give you an idea of what I am trying to create... forget array then... how do I build a Resultset with that information then? — Fizor, Mar 28 '14 at 14:59
Maybe create a temporary table and inside a loop insert your days in one at a time — Bobby, Mar 28 '14 at 15:00
@Mike sorry I thought you wanted just the total, added an answer for a set of days — Alex K., Mar 28 '14 at 15:15
Your question is NOT clear. If there are no arrays in SQL, it's not obvious what you want. Do you want to return a Row with X (31) columns? Do you want to return X (31) rows? And for both of those, containing what data? — Erik Philips, Mar 28 '14 at 15:20

score 1 · Answer 1 · edited May 23 '17 at 10:26

1

Have you tried using DATEDIFF?

edited May 23 '17 at 10:26

Community

1
1

answered Mar 28 '14 at 14:52

Nathan

1,220
3
15
26

score 1 · Answer 2 · answered Mar 28 '14 at 15:03

1

DECLARE @DaysInMonth INT
SET @DaysInMonth = 31

DECLARE @i INT
SET @i = 1

DECLARE @temp TABLE([Days] INT)

WHILE @i <= @DaysInMonth
BEGIN
    INSERT INTO @temp
    VALUES(@i)
    SET @i = @i + 1
END

SELECT *
FROM @temp

answered Mar 28 '14 at 15:03

Bobby

2,830
3
19
36

Probably not the nicest way of doing it but you get all the days you want – Bobby Mar 28 '14 at 15:04

score 1 · Accepted Answer · answered Mar 28 '14 at 15:15

A recursive CTE can provide a days table;

declare @date datetime = '01 feb 1969'
declare @days int = datediff(day, dateadd(day, 1 - day(@date), @date),
                    dateadd(month, 1, dateadd(day, 1 - day(@date), @date)))

;with days(day) as
(
    select 1 as day
        union all
    select day + 1
        from days
        where day < @days
)
select day from days

Count days of month

3 Answers3