Using the DATEDIFF function allows you to easily calculate week days in SQL, because it both removes the time from a date and converts the date into a number for easy mathematical calculations.

Calculating Most Recent Monday

DECLARE@MostRecentMondayDATETIME=DATEDIFF(day, 0, GETDATE()– DATEDIFF(day, 0, GETDATE())%7)

PRINT @MostRecentMonday

 

Calculating Previous Sunday

DECLARE@CurrentWeekdayINT=DATEPART(WEEKDAY,GETDATE())

DECLARE@LastSundayDATETIME=DATEADD(day,–1 *((@CurrentWeekday% 7)– 1),GETDATE())

PRINT @LastSunday

 

Calculating Previous Monday

DECLARE@CurrentWeekdayINT=DATEPART(WEEKDAY,GETDATE())

DECLARE@LastMondayDATETIME=DATEADD(day,–7 *((@CurrentWeekday% 7)– 1),GETDATE())

PRINT@LastMonday

Advertisements