Previous week figures 2

[DBAssam]

Hi - I have query that needs to only include items of post added for the previous complete week. i.e. if I was running it today (Tuesday) it should only list items from Monday of last week to Sunday of last week. Same if I ran it tomorrow. I'm really stuck - any ideas?

 

[checkai]

Declare @DoW int, @Start datetime, @End datetime
Set @DoW=datepart(dw,getDate())-1
Set @End=dateadd(d, Case @DoW When 7 Then 0 Else 0-@DoW End, getDate())
Set @Start=@End-6

set @start = convert(char(10),@start,101)
set @end = convert(char(10),@end,101)


print @start
print @end

this is a good one...i had a heck of a time finding this for the first time...works great though...

"...we both know I'm training to become a cagefighter...see what happens if you try 'n hit me..."

 

[JamesLean]

Try this:

--set Monday as first day of week
SET DATEFIRST 1

DECLARE @today datetime,
	@start datetime,
	@end datetime

--set the current date
SET @today = GETDATE()

--set end date = midnight on last Monday
SET @end = CONVERT(varchar(8), DATEADD(dd, -(DATEPART(dw, @today) - 1), @today), 112)

--set start date = previous Monday
SET @start = DATEADD(dd, -7, @end)

SELECT cols
FROM table
WHERE date_col >= @start
	AND date_col < @end

--James

 

[vongrunt]

I think this will work too.. without convert() (hey rudy ) and not dependent on SET DATEFIRST:

declare @start smalldatetime, @end smalldatetime
set @end = dateadd(wk, datediff(wk, 0, getdate()), 0)
set @start = @end - 7

select blah blah
from myTable
where dateCol >= @start and dateCol < @end

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.
[ba