Determine number of Days in month returns different count for Month of March

Question

Here is a weird one for you all.

I need to determine the number of days in a Month

  ;WITH cteNetProfit AS 
(
   ---- NET PROFIT
    SELECT DT.CreateDate
        , SUM(DT.Revenue) as Revenue
        , SUM(DT.Cost)  as Cost
        , SUM(DT.GROSSPROFIT) AS GROSSPROFIT
        FROM
    (
        SELECT   CAST([createDTG] AS DATE) as CreateDate
             ,   SUM(Revenue) as Revenue
             ,   SUM(Cost)  as Cost
             ,   SUM(REVENUE - COST) AS GROSSPROFIT
        FROM [dbo].[CostRevenueSpecific]
        WHERE   CAST([createDTG] AS DATE) > CAST(GETDATE() - 91 AS DATE)
            AND CAST([createDTG] AS DATE) <= CAST(GETDATE() - 1 AS DATE)
        GROUP BY createDTG
        UNION ALL
        SELECT  CAST([CallDate] AS DATE) AS CreateDate
                ,   SUM(Revenue) as Revenue
                ,   SUM(Cost)   as Cost
                ,   SUM(REVENUE - COST) AS GROSSPROFIT
        FROM abc.PublisherCallByDay
        WHERE   CAST([CallDate] AS DATE) > CAST(GETDATE() - 91 AS DATE)
            AND CAST([CallDate] AS DATE) <= CAST(GETDATE() - 1 AS DATE)
        GROUP BY CALLDATE
    ) DT
    GROUP BY DT.CreateDate 
)
 select distinct MONTH(CREATEDATE), DateDiff(Day,CreateDate,DateAdd(month,1,CreateDate))
FROM cteNetProfit

For some reason it is returning two different results for the month of March 2016 one result is 30 and the other 31(which of course is correct) I validate that the underlying data only has 31 days worth of data for the Month of March. Since Feb is a leap year can this affect the DATEDIFF function. The remaining months return the correct #.

Possible duplicate of [How to determine the number of days in a month in SQL Server?](http://stackoverflow.com/questions/691022/how-to-determine-the-number-of-days-in-a-month-in-sql-server) — Code Different, May 27 '16 at 17:51
Sharing those two march month `CreatedDate` may helps to identify the issue — Arulkumar, May 27 '16 at 17:53
sorry confused by comments, CreateDate has data for each day of the month and there is only 1 group of dates for march - no duplicate dates. So what would be the cause of the duplicate for just march and not for the remaining months.? — Karen Schaefer, May 27 '16 at 18:02
Post your full code (EDIT: and what's inside cteNetProfit? Oh well, in case it'll matter :) — , May 27 '16 at 18:05
select MONTH(CREATEDATE), DateDiff(Day,CreateDate,DateAdd(month,1,CreateDate)),CREATEDATE FROM cteNetProfit should shed some light — Fuzzy, May 27 '16 at 18:20

TLaV · Answer 1 · 2016-05-27T18:51:02.047

The difference comes when you hit the 2016-03-31 date. If you run the query below for 2016-03-30 and 2016-03-31, the results of adding 1 MONTH using DATEADD, in both instances, is 2016-04-30. It returns the last day of the next month.

SELECT DATEADD(MONTH,1,'2016-03-30') , DATEADD(MONTH,1,'2016-03-31')

This syntax seemed to work (courtesy of https://raresql.com/2013/01/06/sql-server-get-number-of-days-in-month/).

SELECT DAY(DATEADD(ms,-2,DATEADD(MONTH, DATEDIFF(MONTH,0,@DATE)+1,0))) AS [Current Month]

score 0 · Answer 2 · answered May 27 '16 at 18:29

0

Thanks for the input, however, I found the solution elsewhere

select Distinct MONTH(CREATEDATE), Day(EOMONTH(CreateDate))
FROM cteNetProfit

answered May 27 '16 at 18:29

Karen Schaefer

99
2
3
9

Determine number of Days in month returns different count for Month of March

2 Answers2