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.

About the author

Bradley Schacht

Bradley Schacht is a Senior Program Manager on the Microsoft Azure Synapse Analytics team based in Jacksonville, FL. He has worked with Microsoft SQL Server and Azure data services since 2009 as a consultant, trainer, and architect. He has co-authored 4 SQL Server and Power BI books, most recently the Microsoft Power BI Quick Start Guide. Bradley enjoys solving interesting problems and teaching others to use new technology. He frequently presents at community events around the country, is a contributor to sites such as SQLServerCentral.com, and is a member of the Jacksonville SQL Server User Group (JSSUG).

3 comments

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Bradley Schacht

Bradley Schacht is a Senior Program Manager on the Microsoft Azure Synapse Analytics team based in Jacksonville, FL. He has worked with Microsoft SQL Server and Azure data services since 2009 as a consultant, trainer, and architect. He has co-authored 4 SQL Server and Power BI books, most recently the Microsoft Power BI Quick Start Guide. Bradley enjoys solving interesting problems and teaching others to use new technology. He frequently presents at community events around the country, is a contributor to sites such as SQLServerCentral.com, and is a member of the Jacksonville SQL Server User Group (JSSUG).

Follow Me