How to get 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 last day of the week when we input any one day of a week?

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

Regards

Maybe this Q can helpyou: http://stackoverflow.com/questions/7168874/get-first-day-of-week-in-sql-server — Amber, Jan 24 '14 at 10:55
And this one: http://stackoverflow.com/questions/1110998/get-day-of-week-in-sql-2005-2008 — Jamiec, Jan 24 '14 at 11:02

score 17 · Accepted Answer · answered Jan 24 '14 at 11:00

17

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 @yourdate to the date you want. The first SELECT will give you the first day and the second SELECT will give you the last date

answered Jan 24 '14 at 11:00

Niklas

12,665
22
76
117

Your 4 must change to 7 to get the 7th day of the week – Pierre Jan 24 '14 at 11:07
5

@Pierre I think he meant work week and not regular week. Although if he had meant a regular week you'd have to change it to 6 to get Sunday. – Niklas Jan 24 '14 at 12:08
Yes, I agree with you! :) – Pierre Jan 24 '14 at 13:06
2

This does not wrap around years, try select dateadd(ww, datediff(ww, 0, convert(date, '01 January 2017')), 0) – Murray Foxcroft Aug 29 '17 at 06:42

score 3 · Answer 2 · edited May 20 '19 at 12:55

3

This solves it and also wraps around year ends:

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

edited May 20 '19 at 12:55

DineshDB

5,663
5
30
45

answered Aug 29 '17 at 06:56

Murray Foxcroft

11,972
4
54
79

score 2 · Answer 3 · answered Jan 24 '14 at 11:31

Try this

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

score 0 · Answer 4 · edited Nov 22 '18 at 02:02

0

With a calendar date already loaded, group can be done like this for all the years existing 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

edited Nov 22 '18 at 02:02

Michal

1,878
21
34

answered Nov 22 '18 at 00:25

Carlos García R.

21
4

How to get first day of the week and last day of the week in sql server 2008?

4 Answers4

Linked