Finding start of week in T-SQL

Wah! So, the first two posts turn out to be HTML posts! Well, probably because I'm not doing much actual dot net programming right now :). Besides, this was too neat not to document. It's a line of SQL commands which will determine the first day of the current week. Could be extended to find the first day of any given week.


declare @weekStart datetime
select @weekStart = dateadd(dd, (datepart(dw, GetDate()) * -1) + 2, GetDate())
select @weekstart


You can also put some more jiggery-pokery round it in order to get midnight on the first day of the week:


declare @weekStart datetime, @now datetime
select @weekStart = dateadd(dd, 0, datediff(dd, 0, dateadd(dd, (datepart(dw, GetDate()) * -1) + 2, GetDate())))
select @weekstart