How to Find the Start and End Dates for the Current Week (and more)


tl:dr

See the attached dbo.WeekDates function.

You don’t know what you’re missing, though.

Prologue

Thanks to some really clever temporal functions that are built into SQL Server, working with dates and times in SQL Server is pretty easy.

The exception to that observation is when we have to work with WEEKs. No other date-part in SQL Server always needs to start on the same day of the week. That’s actually the source of several problems when it comes to calculating things like the Start of Week Date, the End of Week Date, and a whole lot more.

Let’s first