How to Get First and Last Day of a Week in SQL Server

To get the first day of the previous week in SQL Server, use the following code:

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

To get the last day of the previous week:

SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),6)

To get the first day of the current week:

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

To get the last day of the current week:

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

To get the first day of the next week:

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

 

To get the last day of the next week:

The results for week number and day of the week depend on your language settings.

In SQL Server the values for the days of the week are the following:

1 – Monday
2 – Tuesday
3 – Wednesday
4 – Thursday
5 – Friday
6 – Saturday
7 – Sunday

For example, for us_english language setting your session’s DATEFIRST setting has a default value 7 (it means that Sunday is the first day of the week), and for German it is 1 (Monday is the first day of the week):

SET LANGUAGE us_english;
SELECT @@DATEFIRST;

SET LANGUAGE German;
SELECT @@DATEFIRST;

To change the DATEFIRST setting for a session without changing the language setting, use the SET DATEFIRST command.
The DATEFIRST statement sets the first day of the week for the session to a number from 1 through 7.
For example, to set the first day of the week to 5 (Friday), use the following command:

SET DATEFIRST 5;