I've been searching but can't seem to find this answer. Probably not using the right terms. Anyway, I'm trying to build a simple query that sums sales for the current month but I'm not sure how to write the part that calculates it properly when it's the first of a month. For instance on 3/16/2012 I want it to return a start date of 3/1/2012 and an end date of 3/15/2012. On 4/1/2012 it should return 3/1/2012 as a start date and 3/31/2012 as the end date. This way, if I'm summing sales for March, it will include the full month even when the date is 4/1.
Hope that makes sense. Thanks for the help.
declare @date datetime
set @date = getdate()
-- if I'm on the first day, go to yesterday
if datepart(dd, @date) = 1
set @date = dateadd(dd, @date, -1)
-- find first and last day of month . . . do your query