Link to home
Start Free TrialLog in
Avatar of BartWestphal
BartWestphal

asked on

Date Range in MS SQL for a month

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.

- Bart
Avatar of Kyle Abrahams, PMP
Kyle Abrahams, PMP
Flag of United States of America image

UseDatePart
http://msdn.microsoft.com/en-us/library/ms174420.aspx

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
Avatar of BartWestphal
BartWestphal

ASKER

I was thinking along the same terms, but was stuck on how to write the full thing.  I've been trying your suggestion but am getting errors.  Would you write something that I can paste into Management Studio that would return the two values (1st of current month, last date of month or current date).

So the output for today would be:
3/1/2012 and 3/16/2012

But if today were 4/1 the output would be:
3/1/2012 and 3/31/2012

Thanks for the help.
ASKER CERTIFIED SOLUTION
Avatar of Simone B
Simone B
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Alright.  Thanks.  This gives me some food for thought.  I'll have to play with it to get it to work in my circumstances, but this is very helpful.
FYI:  For the current month, passing 3/31/2012 is the same as passing 3/16/2012.  You won't have any records for the future unless you're doing what if scenarios or some kind of future thing with the records.
I tweaked the code above and came up with the following that can be plugged in and then I can use the variables in a SQL statement.  Thanks again for the push in the right direction.
I'll change the @date to getdate() and should be good to go.  The Select at the bottom is just to validate the results and won't be there in the final code.

DECLARE
@date DATETIME
DECLARE
@startdate DATETIME
DECLARE
@enddate DATETIME

SET @date = '5/31/2012'

if  DATEPART(dd,@date) = 1
  BEGIN
  SET @startdate = DATEADD(MM, DATEDIFF(MM, 0, @date-1), 0)
  SET @enddate = DATEADD(ms, - 3, DATEADD(mm, -1, DATEADD(mm, DATEDIFF(mm, 0, @date) + 1, 0)))
  END
else
  BEGIN
  SET @startdate = DATEADD(MM, DATEDIFF(MM, 0, @date), 0)
  SET @enddate = DATEADD(ms, - 3, DATEADD(mm, 0, DATEADD(mm, DATEDIFF(mm, 0, @date) + 1, 0)))
  END

  SELECT @date, @startdate, @enddate