How to get the first day of the week and last day of the week in SQL Server 2008?

Question

How to get the first day of the week and the last day of the week when we enter any one day of the week?

For example, if we enter a date, then the first (Monday) and last (Friday) day should be displayed. that is, if we introduce 24-jan-2014, then 20-jan-2014 and 24-jan-2014 should be displayed.

Hello

Answer 1

Here's how you can do it:

DECLARE @yourdate date = getdate()
Select dateadd(ww, datediff(ww, 0, @yourdate), 0)
Select dateadd(ww, datediff(ww, 0, @yourdate), 4)

You set @yourdateto the desired date. The first SELECT will give you the first day, and the second SELECT will give you the last date

Answer 2

try it

SELECT DATEADD(wk, DATEDIFF(wk, 6, '5/13/2005'), 6)
SELECT DATEADD(wk, DATEDIFF(wk, 5, '5/13/2005'), 5)

(or)

Declare @Date datetime
Det @Date = '2012-04-12'
Delect @Date - DATEPART(dw, @Date) + 1 FirstDateOfWeek,
       @Date + (7 - DATEPART(dw, @Date)) LastDateOfWeek

Answer 3

, :

SELECT DATEADD(wk, DATEDIFF(d, 0, '01 January 2017') / 7, 0)

Answer 4

If the calendar date is already loaded, grouping can be done this way for all years indicated in the table =)

select 
Y,
M,
(Select dateadd(ww, datediff(ww, 0, dt), 0) ) wk_str_dt ,
(Select dateadd(ww, datediff(ww, 0, dt), 4)  )wk_end_dt , 
dt recd_crt_dt
from [tcalendar]
where  isWeekday= 1 
AND DW = 2 -- only mondays
order by Y, W