SQL – Calculate Most Recent Monday, Last Sunday, or Last Monday

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 @MostRecentMonday DATETIME = DATEDIFF(day, 0, GETDATE() DATEDIFF(day, 0, GETDATE()) %7)

PRINT @MostRecentMonday

Calculating Previous Sunday

DECLARE @CurrentWeekday INT = DATEPART(WEEKDAY, GETDATE())

DECLARE @LastSunday DATETIME = DATEADD(day, 1 * (( @CurrentWeekday % 7) 1), GETDATE())

PRINT @LastSunday

Calculating Previous Monday

DECLARE @CurrentWeekday INT = DATEPART(WEEKDAY, GETDATE())

DECLARE @LastMonday DATETIME = DATEADD(day, 7 * (( @CurrentWeekday % 7) 1), GETDATE())

PRINT @LastMonday

 

Leave a Reply

x

We use cookies to ensure the best possible experience on our website. Detailed information on the use of cookies on this site is provided in our Privacy and Cookie Policy. Further instruction on how to disable our cookies can be found there.