Calculate First & Last Day of for 2 Months from current date & First & Last Day of the Weeks going back 4 Weeks

Question

Calculate First & Last Day of for 2 Months from current date & First & Last Day of the Weeks going back 4 Weeks

Welsh Corgi

SSC Guru

Points: 116520
More actions
November 2, 2011 at 1:10 pm

#245442

I need to Calculate the First & Last Day of for 2 Months from current date & First & Last Day of the Weeks going back 4 Weeks.
I was able to calulate a number of other Date Ranges but I would appreciate any help on this.
--First Day of Previous Month
-- OK
SELECT DATEADD(mm, DATEDIFF(mm,0,DATEADD(mm,-1,GETDATE())), 0)
----Last Day of Previous Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
----Last Day of Previous Month - Just before Midnight -- Can use between operator
SELECT DATEADD(s,-2,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
-- Start Monday -- Current Week
SELECT "Start_of_Current_Week" = dateadd(week, datediff(week, 0, getdate()), 0); -- Need Start of Previous Week
SELECT "End_of_Previous_Week" = DATEADD(WEEK, DATEDIFF(WEEK, -1, GETDATE()), -1);
-- Need the Start and End of the 4 Previous Weeks
Thank you.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

I find it easier if you show me what you are looking for, I figure things out better when I have a picture of what is needed. Think you could provide some examples of what you are looking for here?

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 2

Lynn Pettis (11/2/2011)
I find it easier if you show me what you are looking for, I figure things out better when I have a picture of what is needed. Think you could provide some examples of what you are looking for here?

Thanks for your reply.

Sure, sorry about that.

I'm on 2 months back but I don't have the Date Ending down on all but a few, e.x. '2011-11-30 23:59:59.000'

I need buckets on the following: 2010 2011 YTD NOV 2011 MTD OCT 2011 SEP 2011 Week of OCT 30 Week of OCT 23 Week of OCT 9

Listed about are the dynamic headers.

The part that I was asking about would be used in the WHERE Clause with the BETWEEN OPERATOR so that I can generate numbers based on the Start and Ending Date Time Periods.

Thanks again for your help.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 3

Sample Output.

Ran before End of Month (Oct).

Column has a 1 in each record that the lead generated into a sale.

Account Affinity20102011 YTD OCT 2011 MTDSEP 2011 AUG 2011 WK OCT 23Week of OCT 16Week of OCT 9Week of OCT 2

323234Joe's02171405020

434343Sali's000000000

434567Ted's000000000

456723Terri's000000000

222222Alice646981090080

333333Fred's000000000

555555Denise000000000

888888Nancy200000000

999999Independent000000000

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

I think I'm still a little lost. Given date 'x' what are you expecting regarding start date and end date for 2 months? Same for the week start and end dates.

drew.allen SSC Guru Points: 77014 More actions · Answer 5

Welsh Corgi (11/2/2011)
I'm on 2 months back but I don't have the Date Ending down on all but a few, e.x. '2011-11-30 23:59:59.000'

You are potentially missing some records like '2011-11-30 23:59:59.123'

The part that I was asking about would be used in the WHERE Clause with the BETWEEN OPERATOR so that I can generate numbers based on the Start and Ending Date Time Periods.

With datetime fields, it's best not to use the BETWEEN operator precisely because of the situation above. It's better to have two conditions. (It's also easier to calculate the comparisons, because you're using the start of a month in both cases.)

WHERE YourField >= '2011-11-01' -- or '2011-11-01 00:00:00.000'

AND YourField < '2011-12-01' -- or '2011-12-01 00:00:00.000'

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 6

Lynn Pettis (11/2/2011)
I think I'm still a little lost. Given date 'x' what are you expecting regarding start date and end date for 2 months? Same for the week start and end dates.

Date X = @CurrentDate

DECLARE @CurrentDate SmallDateTime

SET @CurrentDate = GetDate()

The StartDateforPreviousTwoMonths would be:

StartDatePreviousMonth

2011-09-01 00:00:00.000

EndDatePreviousMonth

2011-09-30 23:59:58.000

----------------------------------------------------------------------------------------

-- This returns the Start and Ending Date for the Previous Month

SELECT DATEADD(mm, DATEDIFF(mm,0,DATEADD(mm,-1,@CurrentDate)), 0)AS StartDatePreviousMonth

StartDatePreviousMonth

2011-10-01 00:00:00.000

SELECT DATEADD(s,-2,DATEADD(mm, DATEDIFF(m,0,@CurrentDate),0)) AS EndDateLastMonth

EndDateLastMonth

2011-10-31 23:59:58.000

The Start Date for a Week Ends on Monday

The End Date for a Week Ends on Sunday

The End Date for the Previous Week Ends on preceding Sunday

The Start Date for the Previous Week Start on the preceding Monday

Based on the Current Date '2001-11-02'

The Weeks Start on Moday and End on Sunday.

I questioned this and I was told that they did not care as long as I was consistent.

I could change to Sunday through Saturday but I have no control.

The Start Date for the previous Week = '2011-10-24'

The End Date for the previous Week = '2011-10-30'

The Start Date for 2 Weeks Back = '2011-10-17'

The End Date for 2 Weeks Back = '2011-10-23'

The Start Date for 3 Weeks Back = '2011-10-10

The End Date for 3 Weeks Back = '2011-10-16'

The Start Date for 4 Weeks Back = '2011-10-03'

The End Date for 4 Weeks Back = '2011-10-09'

I hope that is clear enough.

The last example was ran in the Month of October. That is why the months a weeks are one off.

Thank you.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 7

Drew,

I do not want to miss any records.

I have already calculated the First Dates of the Current, Previous and Next Months but I thought that the BETWEEN OPERATOR would be preferred? I guess not.

Another issue is that we span a few times zones within the US but I do not want to make it complicated.

Do to the nature of the Business were only have records transferred within normal Business Hours

Thanks.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

First, I'll look at the date manipulations a bit later. Looks like the date manipulation code in my blog on ssc is just what you need with some minor modifications.

Second, I have to agree with Drew. When querying on date ranges, the best method is this:

...

WHERE

SomeDateCol >= @startdate and SomeDateCol < @enddate

...

If you wanted all data for Oct 2011, @startdate = 2011-10-01 00:00:00.000 and @enddate = 2011-11-01 00:00:00.000.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 9

OK, I agree on the Date range.

In the past I would concatenate the Date with the Time '23:59:59.123' and use the Between Operator because of the large number of records.

I usually use >= and the <.

Thanks again.

'

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 10

Lynn Pettis (11/2/2011)
First, I'll look at the date manipulations a bit later. Looks like the date manipulation code in my blog on ssc is just what you need with some minor modifications.

Lynn,

Please don't do those Date Range Calculations.

I have what I need thanks. 🙂

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

Welsh Corgi (11/3/2011)
Lynn Pettis (11/2/2011)
First, I'll look at the date manipulations a bit later. Looks like the date manipulation code in my blog on ssc is just what you need with some minor modifications.
Lynn,
Please don't do those Date Range Calculations.
I have what I need thanks. 🙂

That's good because a bit later never came. 😛