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
BartWestphalAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kyle AbrahamsSenior .Net DeveloperCommented:
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
0
BartWestphalAuthor Commented:
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.
0
Simone BSenior E-Commerce AnalystCommented:
In the code below, you can use anything you like for the @date variable, including getdate().



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

SET @date = 'apr 1, 2012'

SET @enddate = DATEADD(d,-1,@date)

SET @startdate = CAST(datename(m,@enddate) + '1, ' + CAST(YEAR(@enddate) AS CHAR(4)) AS DATETIME)


SELECT @startdate, @enddate
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

BartWestphalAuthor Commented:
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.
0
Kyle AbrahamsSenior .Net DeveloperCommented:
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.
0
BartWestphalAuthor Commented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.