Repository: duckdb/duckdb (language: python)

Issue:
Date Difference Discrepancy in DuckDB
### What happens?

There appears to be a discrepancy in the way DuckDB calculates the difference between two dates in weeks compared to SQL Server.
When calculating the difference in weeks between '2023-01-22 05:30:00+05:30' and '2024-09-13 05:30:00+05:30', the expected result is 85 weeks, as observed in SQL Server. DuckDB returns 86 weeks for the same calculation.


### To Reproduce

SQL Server
```sql
SELECT DATEDIFF(week, CAST('2023-01-22 05:30:00+05:30' AS DATETIMEOFFSET), CAST('2024-09-13 05:30:00+05:30' AS DATETIMEOFFSET));
```
```
Result: 85
```
DuckDB
```sql
SELECT date_diff(
    'WEEK',
    '2023-01-22 05:30:00+05:30'::timestamptz,
    '2024-09-13 05:30:00+05:30'::timestamptz
) AS column;
```
```
Result: 86
```

Questions

1. Is this difference in behavior expected?
2. If not, can DuckDB's behavior be made compatible with SQL Server?
3. If it is expected, could you please explain the reasoning behind DuckDB's calculation method?

Note: Timezone is `Asia/Kolkata`

### OS:

Ubuntu 20.04.5 LTS

### DuckDB Version:

1.0

### DuckDB Client:

python

### Full Name:

Arif Aslam

### Affiliation:

Mammoth Analytics Inc.

### What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.

I have tested with a stable release

### Did you include all relevant data sets for reproducing the issue?

Yes

### Did you include all code required to reproduce the issue?

- [X] Yes, I have

### Did you include all relevant configuration (e.g., CPU architecture, Python version, Linux distribution) to reproduce the issue?

- [X] Yes, I have


Discussion / hints:
No this is a bug. Shouldn't' be hard to fix though.
OK the problem is that MS-SQL uses a specific convention for `date_diff` for every other date part _except_ weeks. That convention is that it counts period boundary crossings (e.g., the first of the month). What they do for weeks is just divide the number of days by 7 and round down. This is what our `date_sub('week', ...)` function does. 

Incidentally, this has nothing to do with ICU - regular dates do the same thing.

Fix patch:
diff --git a/extension/icu/icu-datesub.cpp b/extension/icu/icu-datesub.cpp
index 708dbd529449..c56ccebc9884 100644
--- a/extension/icu/icu-datesub.cpp
+++ b/extension/icu/icu-datesub.cpp
@@ -209,6 +209,17 @@ struct ICUCalendarDiff : public ICUDateFunc {
 		return sub_func(calendar, start_date, end_date);
 	}
 
+	static part_trunc_t DiffTruncationFactory(DatePartSpecifier type) {
+		switch (type) {
+		case DatePartSpecifier::WEEK:
+			//	Weeks are computed without anchors
+			return TruncationFactory(DatePartSpecifier::DAY);
+		default:
+			break;
+		}
+		return TruncationFactory(type);
+	}
+
 	template <typename T>
 	static void ICUDateDiffFunction(DataChunk &args, ExpressionState &state, Vector &result) {
 		D_ASSERT(args.ColumnCount() == 3);
@@ -229,7 +240,7 @@ struct ICUCalendarDiff : public ICUDateFunc {
 			} else {
 				const auto specifier = ConstantVector::GetData<string_t>(part_arg)->GetString();
 				const auto part = GetDatePartSpecifier(specifier);
-				auto trunc_func = TruncationFactory(part);
+				auto trunc_func = DiffTruncationFactory(part);
 				auto sub_func = SubtractFactory(part);
 				BinaryExecutor::ExecuteWithNulls<T, T, int64_t>(
 				    startdate_arg, enddate_arg, result, args.size(),
@@ -248,7 +259,7 @@ struct ICUCalendarDiff : public ICUDateFunc {
 			    [&](string_t specifier, T start_date, T end_date, ValidityMask &mask, idx_t idx) {
 				    if (Timestamp::IsFinite(start_date) && Timestamp::IsFinite(end_date)) {
 					    const auto part = GetDatePartSpecifier(specifier.GetString());
-					    auto trunc_func = TruncationFactory(part);
+					    auto trunc_func = DiffTruncationFactory(part);
 					    auto sub_func = SubtractFactory(part);
 					    return DifferenceFunc<T>(calendar, start_date, end_date, trunc_func, sub_func);
 				    } else {
diff --git a/src/core_functions/scalar/date/date_diff.cpp b/src/core_functions/scalar/date/date_diff.cpp
index d4514b90ef63..6266dda3503c 100644
--- a/src/core_functions/scalar/date/date_diff.cpp
+++ b/src/core_functions/scalar/date/date_diff.cpp
@@ -91,8 +91,8 @@ struct DateDiff {
 	struct WeekOperator {
 		template <class TA, class TB, class TR>
 		static inline TR Operation(TA startdate, TB enddate) {
-			return Date::Epoch(Date::GetMondayOfCurrentWeek(enddate)) / Interval::SECS_PER_WEEK -
-			       Date::Epoch(Date::GetMondayOfCurrentWeek(startdate)) / Interval::SECS_PER_WEEK;
+			//	Weeks do not count Monday crossings, just distance
+			return (enddate.days - startdate.days) / Interval::DAYS_PER_WEEK;
 		}
 	};
 


Test patch:
diff --git a/test/sql/function/timestamp/date_diff.test_slow b/test/sql/function/timestamp/date_diff.test_slow
index 90bc3b64d2cf..00b5da10e785 100644
--- a/test/sql/function/timestamp/date_diff.test_slow
+++ b/test/sql/function/timestamp/date_diff.test_slow
@@ -186,6 +186,17 @@ SELECT DATEDIFF('microsecond' , startdate, enddate) FROM time1
 ----
 86399999999
 
+# MS-SQL Weeks are just days // 7. No Mondays involved...
+query I
+SELECT date_diff('WEEK', '2023-01-22'::date, '2024-09-13'::date) AS column;
+----
+85
+
+query I
+SELECT date_diff('WEEK', '2023-01-22 05:30:00'::timestamp, '2024-09-13 05:30:00'::timestamp) AS column;
+----
+85
+
 # Negation
 foreach datepart hour minute second millisecond microsecond
 
diff --git a/test/sql/function/timestamp/test_icu_datediff.test b/test/sql/function/timestamp/test_icu_datediff.test
index 7842bee53f42..08ed1704b9ad 100644
--- a/test/sql/function/timestamp/test_icu_datediff.test
+++ b/test/sql/function/timestamp/test_icu_datediff.test
@@ -92,7 +92,7 @@ FROM (SELECT '2021-07-30'::TIMESTAMP + INTERVAL (d) DAY AS startdate FROM range(
 
 endloop
 
-# Week starts on Monday for ICU
+# Week diffs are just day counts
 query III
 SELECT *, DATE_DIFF('week', lo, hi)
 FROM (
@@ -104,14 +104,14 @@ FROM (
 2022-09-01 15:00:00-07	2022-09-02 07:00:00-07	0
 2022-09-02 15:00:00-07	2022-09-03 07:00:00-07	0
 2022-09-03 15:00:00-07	2022-09-04 07:00:00-07	0
-2022-09-04 15:00:00-07	2022-09-05 07:00:00-07	1
+2022-09-04 15:00:00-07	2022-09-05 07:00:00-07	0
 2022-09-05 15:00:00-07	2022-09-06 07:00:00-07	0
 2022-09-06 15:00:00-07	2022-09-07 07:00:00-07	0
 2022-09-07 15:00:00-07	2022-09-08 07:00:00-07	0
 2022-09-08 15:00:00-07	2022-09-09 07:00:00-07	0
 2022-09-09 15:00:00-07	2022-09-10 07:00:00-07	0
 2022-09-10 15:00:00-07	2022-09-11 07:00:00-07	0
-2022-09-11 15:00:00-07	2022-09-12 07:00:00-07	1
+2022-09-11 15:00:00-07	2022-09-12 07:00:00-07	0
 
 query I
 SELECT date_diff('week', '2015-10-06 04:22:11'::timestamptz, '2016-11-25 23:19:37'::timestamptz);