Improper calculation of a date difference in sql-CodePudding

I need to calculate the difference between DFU.HISTSTART and the last Sunday which is for today is 2/27. It should be dynamic and change every Sunday.

For some reason for this calculation I am getting 3 and should get 4.

,ABS(DATEDIFF(wk,
     DATEADD(wk, 
     DATEDIFF(wk,6,GETDATE()), 0), DFU.HISTSTART)) 
AS '#WKS of Hist'

Does someone have any ideas?

CodePudding user response：

You have two problems... the first is that you're trying to do the old offset trick with the "6". That works on other date parts but not on week. From the Microsoft Documentation...

For a week (wk, ww) or weekday (dw) datepart, the DATEPART return value depends on the value set by SET DATEFIRST.

If your DATEFIRST is set to 7 (you can verify by running SELECT @@DATEFIRST;) AND your weeks start on Sundays, the following will work just fine and return a "4".

--===== Setup just the dates in question for a demo
DECLARE @HistStart DATE = '01-30-22'
        ,@Today    DATE = '02-27-22'
;
--===== Demo the "right" way to use "wk".
     -- I say "right" way because I don't trust DATEFIRST.
 SELECT DATEDIFF(wk,@HistStart,@Today)
;
GO

The second thing is that it's generally a really bad practice to depend on the DATEFIRST setting in this global computing environment. Instead, do the much more universal/bullet-proof method of using Integer math to calculate the number of weeks it's been since date-serial 6, which you correctly identified as a Sunday.

--===== Setup just the dates in question for a demo
DECLARE @HistStart DATE = '01-30-22'
        ,@Today    DATE = '02-27-22'
;
--===== Demo "Bulletproof" Way to calculate the difference in Weeks starting on Sunday
 SELECT DATEDIFF(dd,6,@Today)/7 - DATEDIFF(dd,6,@HistStart)/7
;

If you need to calculate week differences in weeks a lot, you might want to turn that into a function so that if the company decides to change the day of the week that is the start of the week, you'll only need to change it in one place. In fact, you might want to have the function read it (the date-serial for the starting day of the week) from a "general settings table".

CodePudding user response：

Another way of doing this:

with last_sunday as (
  SELECT
  case DAYNAME(current_date())
  when 'Sun' then current_date()
  when 'Mon'then current_date()-1
  when 'Tue'then current_date()-2
  when 'Wed'then current_date()-3
  when 'Thu'then current_date()-4
  when 'Fri'then current_date()-5
  when 'Sat'then current_date()-6
  else '2020-01-01' end "SUNDAY_DATE"
)
SELECT
DFU.HIST_START_DATE
,LAST_SUNDAY.SUNDAY_DATE
,datediff(week,DFU.HIST_START_DATE,LAST_SUNDAY.SUNDAY_DATE) weeks_diff
FROM DFU
JOIN LAST_SUNDAY
;