1

Spent the better half of the day trying to figure this one out.

I want to get the datediff of two dates, based on their ISO week.

Here is my code: 

SET DATEFIRST 1 ;   

DECLARE @A date, @B date;

SET @A = '20180829'; -- August 29th
SET @B = '20180902'; -- September 2nd

SELECT DATEDIFF(WW, @A, @B )

If you check: http://whatweekisit.org/ (Week 35, Year 2018) you can see that it runs August 27 to September 2.

The code above will return a DateDiff = 1, which should be 0. Trying to run DateDiff on ISO week just returns the following error:

The datepart iso_week is not supported by date function datediff

I've tried taking out the week dateparts from the dates, but then I get the problem when comparing dates from different years.

Is there a way around this?

Agneum
  • 727
  • 7
  • 23

3 Answers3

2

So after some reading, there's not a native way to do it. But here's a workaround:

DECLARE @A DATE = '20180829' -- August 29th
DECLARE @B DATE = '20180902' -- September 2nd

--We need to back each date up to the first day of its week.
DECLARE @A_FIRSTWEEKDAY DATE = DATEADD(DAY, -(DATEPART(WEEKDAY, @A) - 1), @A)
DECLARE @B_FIRSTWEEKDAY DATE = DATEADD(DAY, -(DATEPART(WEEKDAY, @B) - 1), @B)

/*The WEEKDAY part counts Sunday as day 1. If the original date was
a Sunday, it backed up zero days and it still needs to back up six
days. If it was any other day, it backed all the way up to Sunday
and now it needs to move forward one day.*/
IF DATEPART(WEEKDAY, @A) = 1
     BEGIN SET @A_FIRSTWEEKDAY = DATEADD(DAY, -6, @A_FIRSTWEEKDAY) END
ELSE BEGIN SET @A_FIRSTWEEKDAY = DATEADD(DAY,  1, @A_FIRSTWEEKDAY) END
IF DATEPART(WEEKDAY, @B) = 1 
     BEGIN SET @B_FIRSTWEEKDAY = DATEADD(DAY, -6, @B_FIRSTWEEKDAY) END
ELSE BEGIN SET @B_FIRSTWEEKDAY = DATEADD(DAY,  1, @B_FIRSTWEEKDAY) END

--Now we can just difference the weeks.
SELECT DATEDIFF(DAY, @A_FIRSTWEEKDAY, @B_FIRSTWEEKDAY) / 7
Adam C
  • 95
  • 1
  • 10
  • This runs into the issue that I will only get the week difference, not the cumulative number of weeks since 2005: SET @A = '20050129'; SET @B = '20180902'; Result: 31, even though they are years apart. – Agneum Aug 29 '18 at 21:15
  • You're right. I've updated with another solution that accounts for different years. – Adam C Aug 30 '18 at 23:12
  • Yes, though you need to add a missing bracket to your FIRSTWEEKDAY vars. Is your method any more flexible/correct/faster than below method? – Agneum Sep 03 '18 at 07:06
  • 1
    @starbyone, I've fixed the missing parentheses. The method you posted is equally accurate. It's up to you to choose which you prefer. Mine is super verbose... I'd likely prefer the one you found if I hadn't come up with mine myself. :) – Adam C Sep 05 '18 at 21:57
1
SELECT DATEDIFF(ww, DATEADD(dd,-1, @A ), DATEADD(dd,-1,@B)) 
--Seems to do the trick?

Taken from: Number of weeks and partial weeks between two days calculated wrong

Though I cannot see why the other post in the link adds a 1 at the end.

Agneum
  • 727
  • 7
  • 23
  • What is the cast for? – shawnt00 Aug 29 '18 at 16:30
  • 1
    I think that -1 is going to vary year to year. The idea is to translate dates by an appropriate number of days so that `datediff`´s Sunday logic will work. – shawnt00 Aug 29 '18 at 16:31
  • @shawnt00: Cast is because some logic uses dates as primary keys (as ints), not related to the problem - I will edit this out. – Agneum Aug 29 '18 at 21:20
1

This is because DATEDIFF always uses Sunday as the first day of the week to ensure the function operates in a deterministic way: https://learn.microsoft.com/datediff-transact-sql

So 20180902 (Sunday) is first day of next week.

Pm Duda
  • 741
  • 5
  • 16