Calculate the Date of Previous Sunday

There are a number of ways that you can calculate dates using T-SQL.  A common practice is to find the first day of the week which can be accomplished easily by using the following function.

SELECT DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0)

However, you will notice that this returns the date for Monday as SQL Server uses Monday for the start of the week. If your week starts on Sunday or you need to get the date for the previous Sunday you can simply modify the DATEADD part of the query from adding zero to adding negative 1.  So our query to get the date for the Sunday now becomes the following.

SELECT DATEADD(wk, DATEDIFF(wk,0,GETDATE()), -1)

One last modification if you do not want to have the timestamp attached is to add a conversion on the beginning.  So now we get the date and no time.

SELECT CONVERT(DATE,DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0))

This is all predicated on the DATEFIRST option in SQL Server being set to Monday.  If it is already Sunday then the -1 is not necessary.

Bradley Schacht

Bradley Schacht is a Cloud Solution Architect on the state and local government team with Microsoft based in Jacksonville, FL. He has co-authored 3 SQL Server books including "SQL Server 2014 Professional Administration". As a former consultant and trainer, he uses his experience on many parts of the Microsoft BI and data platform to help customers deliver the best possible solutions. Bradley frequently presents at community events around the country. He is a contributor to sites such as SQLServerCentral.com and an active member of the Jacksonville SQL Server User Group (JSSUG).

You may also like...

1 Response

  1. Carl Russell says:

    Thanks for the help with finding the previous Sunday. It works perfectly.

Leave a Reply