Solved

Date Range in MS SQL for a month

Posted on 2012-03-16
6
498 Views
Last Modified: 2012-03-16
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
Comment
Question by:BartWestphal
  • 3
  • 2
6 Comments
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 37729806
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
 

Author Comment

by:BartWestphal
ID: 37730081
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
 
LVL 11

Accepted Solution

by:
Simone B earned 400 total points
ID: 37730167
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Closing Comment

by:BartWestphal
ID: 37730257
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
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 37730467
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
 

Author Comment

by:BartWestphal
ID: 37731291
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

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now