Deterministic Date Range value using BETWEEN

[SQLHacker]

I'm using SQL Server 2005, trying to build a stored procedure or even a DTS (or SSIS now...) package that will execute properly for "Last Week" no matter when the procedure is run.

I am trying to figure out how to configure the starting and ending datetime values to always set to the beginning of the previous week (assuming Sunday is the setting in DATEFIRST) and the end of the previous week.

For example, no matter what day or time the query is executed by a user, I need the first (starting) datetime value to set to Sunday's date, at 12:00:00.003, and the second (ending) value to be set to Saturday's date at 11:59:59.997--FOR THE PREVIOUS WEEK.

I thought I was pretty good at using datepart, and I tried using that, with limited results. I've been able to get part of the way there with the following:

SELECT * FROM SomeTable
WHERE DateAndTime BETWEEN getdate() - 7 AND getdate() -1

However, that is a "moving" or hard coded value (7 and 1), which is not going to work. Also, the getdate() function works great, and includes the time when the query is run, so anything that was done before the current time 7 days ago is omitted.

I'm sure this can be done using a CASE statement (or 2) and CONVERT or CAST. I've used a lot of CASE statements, but have never used (very well) CONVERT or CAST.

Any ideas?

 

[AlexCuse]

This will give you what you need to get the last sunday, with the time portion removed:

[COLOR=blue]declare[/color] @d [COLOR=#FF00FF]datetime[/color]
[COLOR=blue]set[/color] @d = [COLOR=#FF00FF]getdate[/color]()-1

[COLOR=blue]select[/color] [COLOR=#FF00FF]dateadd[/color]([COLOR=#FF00FF]day[/color], 1- [COLOR=#FF00FF]datepart[/color](dw, @d), [COLOR=#FF00FF]dateadd[/color]([COLOR=#FF00FF]day[/color], [COLOR=#FF00FF]datediff[/color]([COLOR=#FF00FF]day[/color], 0, @d), 0))
[COLOR=blue]as[/color] LastSunday

Play arround with this a bit, and I think you will find out what you need to do to get the upper bound. Also, I would forget about the 3 milliseconds, and just use > abd <, rather than between.

Hope this helps,

Alex

[small]----signature below----[/small]
With all due respect, Don Bot, I don't think we should rely on an accident happening. Let's kill him ourselves.

Ignorance of certain subjects is a great part of wisdom

 

[gmmastros]

As long as your DateFirst is set to 1, this should work.

[COLOR=blue]SELECT[/color] * 
[COLOR=blue]FROM[/color]   SomeTable
[COLOR=blue]WHERE[/color]  DateAndTime >= [COLOR=#FF00FF]DateAdd[/color]([COLOR=#FF00FF]Week[/color], [COLOR=#FF00FF]DateDiff[/color]([COLOR=#FF00FF]Week[/color], 0, [COLOR=#FF00FF]GetDate[/color]()), 0) - 8
       And DateAndTime < [COLOR=#FF00FF]DateAdd[/color]([COLOR=#FF00FF]Week[/color], [COLOR=#FF00FF]DateDiff[/color]([COLOR=#FF00FF]Week[/color], 0, [COLOR=#FF00FF]GetDate[/color]()), 0) - 1

To make sure your DateFirst is set properly...

[tt][blue]Select @@DateFirst[/blue][/tt]

If this returns a 1, then the query above will work.

-George

"the screen with the little boxes in the window." - Moron