Evil Science A whole load of stuff

Get the first day of the week from a datetime

select dateadd(d ,-(datepart(weekday,getdate()-1)), getdate())

This is straightforwards: the patepart function extracts the weekday from the date, we remove one from that value, turn it to a negative number and then add it back the datetime. Simple.