T-sql Monday before date

Question

I work at a college and our student management systems academic year start date is determined by the Monday on or before the 1st of August. I need to match this in my query, is there a way to easily get the date of the Monday on or before this date.

Answer 1

The accepted answer didn't work for me because I needed both a Sunday week and a Monday week in the same query. This works across different "datefirst" settings:

SELECT DATEADD(d, -((DATEPART(WEEKDAY, '20110515') - DATEPART(dw, '19000101') + 7) % 7), '20110515')

"DATEPART(dw, '19000101')" will determine your "datefirst" setting since 1900-01-01 was on a Monday. If you want a Tuesday based week, you can change 19000101 to 19000102.

BTW, '20110515' is the only date format that works across all SQL Server culture settings. Dates like '2011-05-06' will get mis-interpreted in certain countries. (credit to Itzik Ben-Gan for pointing this out)

Answer 2

set datefirst 1; -- Make Monday the first day of the week

select dateadd(dd, -1*(datepart(dw, '2009-08-01')-1), '2009-08-01')

Returns July 27th, 2009, which is the Monday on or before August 1. Change it to 2005 when Aug 1 was a Monday and the query will return 08-01