Solved

Date Range in MS SQL for a month

Posted on 2012-03-16
6
500 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

776 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