8

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

Gautam Seshadri
  • 155
  • 2
  • 3
  • 10

4 Answers4

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

Niklas
  • 13,005
  • 23
  • 79
  • 119
  • 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
  • Also remember that DATEDIFF(wk) is based on Sunday's only. See the following MS link for proof of that. https://learn.microsoft.com/en-us/sql/t-sql/functions/datediff-transact-sql#remarks – Jeff Moden Jul 22 '22 at 17:11
3

This solves it and also wraps around year ends: 

SELECT DATEADD(wk, DATEDIFF(d, 0, '01 January 2017') / 7, 0)
DineshDB
  • 5,998
  • 7
  • 33
  • 49
Murray Foxcroft
  • 12,785
  • 7
  • 58
  • 86
2

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
Vignesh Kumar A
  • 27,863
  • 13
  • 63
  • 115
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
Michal
  • 2,078
  • 23
  • 36
Carlos García R.
  • 21
  • 5