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

You could use datepart to get the weekday, and then do a little math to back into your monday. This example is using the US default of datefirst 7 (in which Monday is day 2 of the week). Adjust the days to add to be which day of the week Monday is for your locale.

select dateadd(dd, -datepart(dw, '2009-08-01') + 2, '2009-08-01')

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

Answer 3

very hacky

DECLARE @weekday int

SELECT @weekday = DATEPART(WEEKDAY, '1-Aug-2009')

SELECT CASE
 WHEN @weekday = 1 THEN '1-Aug-2009' ELSE DATEADD ( dd,(@weekday-2)*-1, '1-Aug-2009') 
 END

Answer 4

This is a generic algorithm that will return the first Monday of any month (@inputdate):

DATEADD(wk, DATEDIFF(wk, 0, dateadd(dd, 6 - datepart(day, @inputDate), @inputDate)), 0)

It is a common method for getting the first monday of the month in SQL Server. This link explains how the above calculation works along with many other date calculations.

Here is how the above algorithm could be used to get the Monday on or before the 1st day of a month:

-- Set month to get Monday before or at 1st of month.
DECLARE @inputDate DATETIME
SET @inputDate = '2009-08-01'

-- Get first Monday of month.
DECLARE @firstMonday DATETIME
SET @firstMonday = DATEADD(wk, DATEDIFF(wk, 0, dateadd(dd, 6 - datepart(day, @inputDate), @inputDate)), 0)

-- Determine date for first Monday on or before 1st of month.
DECLARE @startDate DATETIME
SET @startDate = @firstMonday
IF @firstMonday > @inputDate
    SET @startDate = DATEADD(wk, -1, @firstMonday)

SELECT @startDate