Get Current week days from monday to sunday

Question

I have tried solution for this is like.

select dateadd(wk, datediff(wk, 0, getdate()), 0)as StartDate ,
   (select dateadd(wk, datediff(wk, 0, getdate()), 0) + 5) as EndDate

it gives monday-saturday in result, but on Sunday it gives me next week days

I want sunday as last day of week and Monday as First Day of week..

Please Help...

Answer 1

In general, use SET DATEFIRST 1 to specify that monday is the first day of the week. However, that doesn't solve the issue here. Use this syntax instead:

SELECT DATEADD(week, DATEDIFF(day, 0, getdate())/7, 0) AS StartWeek,
       DATEADD(week, DATEDIFF(day, 0, getdate())/7, 5) AS EndWeek

Demo

SET DATEFIRST (Transact-SQL)

1    Monday
2    Tuesday
3    Wednesday
4    Thursday
5    Friday
6    Saturday
7    (default, U.S. English) Sunday

Answer 2

You just add 6 days instead of 5.

select dateadd(wk, datediff(wk, 0, getdate()), 0) as StartDate 
select dateadd(wk, datediff(wk, 0, getdate()), 0) + 6) as EndDate