0

These seem a bit verbose and maybe over-complicated; can I simplify and/or make them more readable?

They are returning the dates (as integer type) of the previous week's Monday and Sunday.

declare @sDate int = CONVERT(CHAR(8),DATEADD(wk, DATEDIFF(wk,0,GETDATE())-1, 0),112),
    @edate int = CONVERT(CHAR(8),DATEADD(wk, DATEDIFF(wk,0,GETDATE()), -1),112);
whytheq
  • 34,466
  • 65
  • 172
  • 267

2 Answers2

3

From my experience, if you convert a date to char for any reason other than presentation, you have made a mistake. Also - if you call GetDate twice, you should expect different answers.

DECLARE
  @Now datetime,
  @WeekStart datetime,
  @LastWeekStart datetime,
  @LastWeekEnd datetime

SET @Now = GetDate()
SET @WeekStart = DateAdd(wk, DateDiff(wk, 0, @Now), 0) --standard time trimmer
SET @LastWeekStart = DateAdd(wk, -1, @WeekStart)
SET @LastWeekEnd = DateAdd(dd, -1, @WeekStart)

SELECT @Now, @WeekStart, @LastWeekStart, @LastWeekEnd

Also be aware that there is a sql setting that controls where sql server thinks that the week starts, so this code may not give Monday->Sunday depending on that setting.

Amy B
  • 108,202
  • 21
  • 135
  • 185
2

The simplest way is to use a calendar table. A calendar table might simplify your query to something along these lines. 

-- Select the previous Sunday. The right inequality operator depends
-- on exactly what *you* mean by "previous".
select max(calendar_date)
from calendar
where calendar_date < current_date
  and calendar_day_of_week = 'Sun';
Community
  • 1
  • 1
Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185