Does SQL automatically calculate month to month start and end dates?

Question

I am trying to get total amount of sales sold in 1 month. For example, from 4/1/15 - 4/30/15.

I am getting a span of 3/30/15 - 4/30/15.

Here is my SQL:

    SELECT  Customer.custno
      , Customer.enteredDate AS 'Date Entered'
      , COUNT(BasicPolInfo.polid) AS 'Number of Policies'
      , SUM(COUNT(BasicPolInfo.polid)) OVER (ORDER BY Customer.custno ROWS 
    BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS TotalAmount
    FROM    Customer
        INNER JOIN BasicPolInfo ON Customer.custid = BasicPolInfo.custid
    WHERE   BasicPolInfo.polid IS NOT NULL
        AND Customer.firstname IS NOT NULL
        AND Customer.enteredDate BETWEEN DATEADD(MONTH, -1, GETDATE()) AND
        DATEADD(MONTH, 0, GETDATE())
    GROUP BY Customer.custno
      , Customer.firstname
      , Customer.lastname
      , Customer.entereddate
    ORDER BY Customer.enteredDate ASC

The results I am getting are from the dates 2015-04-30 to 2015-03-30. I am trying to get 2015-04-30 to 2015-04-01.

And for the next month to also be from 2015-05-31 to 2015-05-01

Any help would be appreciated! Thank you!

http://stackoverflow.com/questions/11743810/how-to-get-first-and-last-day-of-previous-month-with-timestamp-in-sql-server — Tony Hinkle, Apr 30 '15 at 19:17

SQLChao · Accepted Answer · 2015-04-30T19:50:57.617

3

Since you are on 2012 you can use EOMONTH()

WHERE Customer.enteredDate >= DATEADD(DAY, 1, EOMONTH(GETDATE(), -1)) 
AND  < DATEADD(DAY, 1, EOMONTH(GETDATE()))

or you could match the month and year only.

where month(Customer.enteredDate) = month(getDate()) 
and year(Customer.enteredDate) = year(getDate())

edited Apr 30 '15 at 19:50

answered Apr 30 '15 at 19:26

SQLChao

7,709
1
17
32

this returns 2015-04-29 to 2015-04-01. – BarcodePiglet Apr 30 '15 at 19:47
I like the `GETDATE()` option, that's the easiest for MS. – Muhammad Abdul-Rahim Apr 30 '15 at 19:49
@DanielKim I have corrected the first part. The reason it wasn't pulling the current day is because of any time stamps. – SQLChao Apr 30 '15 at 19:53

score 1 · Answer 2 · answered Apr 30 '15 at 19:19

1

Customer.enteredDate >= DATEADD(MONTH, DATEDIFF(MONTH, 0, current_timestamp), 0) 
AND  Customer.enteredDate < DATEADD(MONTH, DATEDIFF(MONTH, 0, current_timestamp) + 1, 0)

answered Apr 30 '15 at 19:19

Joel Coehoorn

399,467
113
570
794

Does SQL automatically calculate month to month start and end dates?

2 Answers2