attachments/rhutton/lists/photos/110314_2044_sqlcalculat1.png

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