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

To get the first or the last day of the week in SQL Server, you can use the following code:

SELECT DATEADD(wk, -1, DATEADD(DAY, 1-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE()))) --first day previous week
SELECT DATEADD(wk, 0, DATEADD(DAY, 1-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE()))) --first day current week
SELECT DATEADD(wk, 1, DATEADD(DAY, 1-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE()))) --first day next week

SELECT DATEADD(wk, 0, DATEADD(DAY, 0-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE()))) --last day previous week
SELECT DATEADD(wk, 1, DATEADD(DAY, 0-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE()))) --last day current week
SELECT DATEADD(wk, 2, DATEADD(DAY, 0-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE()))) --last day next week

First and Last Day of a Week in SQL Server

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;

language_setting_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;

If the post helped you, please share it:
Pin It

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

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">