CODEWITHSUNDEEP
sqlsql-server
MattC
MattC

Reputation: 135

Counting the # of days in each month, subsequent of a date

I'm trying to see if I can calculate the # of days in each month, after a given date for a certain # of days.

For example, I have a date of 2019-09-25. If I am planning for the next 105 days, how many of those days are in September, October, November, and so on?

Declare @dtdate date = '20190925',
@days int= 105

Select 
datediff(dd,@dtdate,eomonth(@dtdate)) as DaysSeptember
,datediff(dd,eomonth(@dtdate),eomonth(dateadd(m,1,@dtdate))) as DaysOctober

Upvotes: 0

Views: 75

Answers (3)

dwill
dwill

Reputation: 13

Perhaps not the best solution (not set based), but an alternative approach:

DECLARE @dtdate      DATE = '20190925'
       ,@days        INT  = 105
       ,@DaysInMonth INT

DECLARE @results TABLE
    (
        ResultsID   INT        NOT NULL IDENTITY PRIMARY KEY
       ,YearMonth   VARCHAR(7) NOT NULL
       ,DaysInMonth INT        NOT NULL
    )


WHILE @days > 0
    BEGIN
        SET @DaysInMonth = DATEDIFF(dd, @dtdate, EOMONTH(@dtdate))
        SET @DaysInMonth = IIF(@days > @DaysInMonth, @DaysInMonth, @days)

        INSERT INTO @results
            (
                YearMonth
               ,DaysInMonth
            )
        SELECT  CONVERT(VARCHAR(7), @dtdate, 120)
               ,@DaysInMonth

        SET @days -= @DaysInMonth
        SET @dtdate = DATEADD(dd, 1, EOMONTH(@dtdate))
    END


SELECT  *
FROM    @results AS r

Upvotes: 0

Deepstop
Deepstop

Reputation: 3827

In postgresql I'd do this by

SELECT date_part('month', d), count(d)
FROM generate_series('2019-09-25'::date, '2019-09-25'::date + INTERVAL '105 days', INTERVAL '1 day') series (d)
GROUP BY date_part('year',d), date_part('month', d)
ORDER BY date_part('year',d), date_part('month', d)

Clearly you're not using postgresql but perhaps this will give you a hint. The trick is to create a series of dates within the interval that you can then count. Here'st the output with the month and number of days. Note there are 106 days because the interval is inclusive of the beginning and end dates.

9;6
10;31
11;30
12;31
1;8

Upvotes: 0

M. Kanarkowski
M. Kanarkowski

Reputation: 2205

It looks to me like Sql server. You can do it by simply counting days in each month. Doing it this way has an advantage of flexibility. You can simply change @dtdate, @days and the query will work despite of changing number of months.

DECLARE @dtdate date = '20190925', 
@days int= 105
,@dtmax date;
set @dtmax = dateadd(day, @days, @dtdate);

WITH cte AS (
  SELECT DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY 1/0), @dtdate) AS d
  FROM sys.objects s, sys.objects s2
)
select
    year(d) as year, datename(month, d) as month, count(*) as NumberOfDays
from cte
where d between @dtdate and @dtmax
group by year(d), datename(month, d)
order  by year(d), month

result:

year    month   NumberOfDays
2019    December    31
2019    November    30
2019    October     31
2019    September   5
2020    January     8

Upvotes: 3

Related Questions