• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 557
  • Last Modified:

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
0
BartWestphal
Asked:
BartWestphal
  • 3
  • 2
1 Solution
 
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
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
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
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.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now