1

I have a query for calculating first and last date in the week, according to given date. It is enough to set @dDate and the query will calculate first (monday) and last date (sunday) for that week.

Problem is, that is calculating wrong and I don't understand why.

Example:

@dDate = 2019-10-03 (year-month-day).

Result:

W_START      W_END
2019-09-25  2019-10-01

But it should be:

2019-09-30  2019-10-06

Why is that?

Query:

set datefirst 1
declare @dDate date = cast('2019-10-16' as date)
select @dDAte
declare @year int = (select DATEPART(year, @dDAte))
select @year
declare @StartingDate date = cast(('' + cast(@year as nvarchar(4)) + '-01-01') as date)
select @StartingDate


declare @dateWeekEnd date = (select DATEADD(week, (datepart(week, cast(@dDate as date)) - 1), @StartingDate))
declare @dateWeekStart date = dateadd(day, -6, @dateWeekEnd)

select @dateWeekStart W_START, @dateWeekEnd W_END
FrenkyB
  • 6,625
  • 14
  • 67
  • 114

1 Answers1

3

Days of the week are so complicated. I find it easier to remember that 2001-01-01 fell on a Monday.

Then, the following date arithmetic does what you want:

select dateadd(day,
               7 * (datediff(day, '2001-01-01', @dDate) / 7),
               '2001-01-01'  -- 2001-01-01 fell on a Monday
              )

I admit this is something of a cop-out/hack. But SQL Server -- and other databases -- make such date arithmetic so cumbersome that simple tricks like this are handy to keep in mind.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • With your query I don't have to set DATEFIRST = 1? It's always taking monday as first day of the week? – FrenkyB Mar 12 '17 at 14:11
  • 1
    @FrenkyB . . . It is using the fact that 2001-01-01 fell on a Monday. That fact is not affected by `DATEFIRST`. That fact is not affected by language settings. That fact is not affected by other internationalization settings. That fact is true regardless of the database and operating system, so similar logic can be used across databases (although the syntax varies significantly). – Gordon Linoff Mar 12 '17 at 14:33
  • Thanks for answer, it works. If you find time can I ask to look into my another question: http://stackoverflow.com/questions/42758099/number-of-week-between-two-days-calculated-wrong – FrenkyB Mar 13 '17 at 07:13