CODEWITHSUNDEEP
sqlsql-serverdate
Gautam Seshadri
Gautam Seshadri

Reputation: 175

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

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

Upvotes: 10

Views: 25334

Answers (4)

Murray Foxcroft
Murray Foxcroft

Reputation: 13795

This solves it and also wraps around year ends: 

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

Upvotes: 3

Carlos García R.
Carlos García R.

Reputation: 21

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

Upvotes: 0

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28413

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

Upvotes: 2

Niklas
Niklas

Reputation: 13155

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

Upvotes: 19

Related Questions