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
Hope that makes sense. Thanks for the help.
- Bart
Microsoft SQL ServerMicrosoft SQL Server 2008Microsoft SQL Server 2005
Last Comment
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.
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
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
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.
ASKER
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
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
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