2

I have this query to get the start of the week (Monday) for the current date. It breaks when the date is first of the month and a Sunday. Not sure how to solve this issue. Any help would be highly appreciated. Thanks.

DATEADD(wk,DATEDIFF(wk,0,'2017-01-01'),0)
radical
  • 23
  • 5
  • I use this in my queries: `SET DATEFIRST 1 select DATEADD(dd, (1-DATEPART(dw, '2017-01-01')), '2017-01-01')` (SET DATEFIRST 1 is required to fix the American date style left behind by default by the DBA) – cha Mar 30 '16 at 04:30
  • I tried SET DATEFIRST 1, the issue still persists. – radical Mar 30 '16 at 06:31

2 Answers2

1

This is happening because you are finding the no. of weeks from date 0 to the supplied date. 0 is actually '1900-01-01' which was a Monday. Therefore,DATEDIFF finds the number of completed weeks from this date until the supplied data. Thats why DATEDIFF for March 27 2016 returns 6065 (as it is the end of a week) and March 26 2016 returns 6064 (as it is still not the end of a week). This is already explained in this link - Get first day of the week

Community
  • 1
  • 1
Amit Sukralia
  • 950
  • 1
  • 5
  • 13
1

Try the code mentioned below. Is that what you are looking for?. Let me know if this works

declare @date date = '2017-01-01'
select case when dateadd(dd,-datepart(dw,@date)+2,@date) < dateadd(dd,-day(@date)+1,@date)
                        then @date else dateadd(dd,-datepart(dw,@date)+2,@date) end as firstdayoftheweek
sam
  • 1,242
  • 3
  • 12
  • 31