6 weekday time frame

I was wondering how I can do a date query. I have a table with order_quant and date. What I would like to do is get an output of the last 6 weeks on a specific day of the week. Last 6 orders for Tuesdays. Or, last 6 orders for Friday. I just can't get it narrowed down to just a weekday, just all order for 6 weeks. Any ideas?

WHERE datepart( dw, MyDateColumn ) = 3

is Tuesday, for default US settings where the first day of the week is Sunday. However, you can change that by using the DateFirst setting.

To verify your setting:

SELECT @.@.DATEFIRST

To change your setting: (In this situation, change to Monday as the first day of the week.)

SET DATEFIRST 1

|||

You could use DATEPART function for determine week day and DATEDIFF for difference

Code Snippet

createtable #orders

(

order_quant int,

date datetime

)

insertinto #orders values(10,'2007-06-05')

insertinto #orders values(20,'2007-05-30')

insertinto #orders values(30,'2007-05-23')

insertinto #orders values(40,'2007-05-22')

insertinto #orders values(50,'2007-06-03')

insertinto #orders values(60,'2007-04-10')

select*from #orders wheredatepart(weekday,date)=3 --Tuesdays

select*from #orders wheredatepart(weekday,date)=3 anddatediff(week,date,getdate())<=6 --Tuesdays, 6 weeks or later

|||That did it for me it looks like, thanks. I just need to remember to deal with week '0' but shouldn't be a problem for me.