Fix for TDVT datediff.sow.week tests. This was necessary to properly handle operations that find the difference in weeks between two dates when using a Start Of Week (SOW) parameter that is different from the default of "Sunday". For example, many countries in Europe and Asia use "Monday" as the first day of the week and some countries in Africa use "Saturday" as the first day of the week.
Detailed Explanation of Fix
The motivation for this fix is that it is sometimes not straightforward to determine which week a date falls in. For example, in the United States, Sunday is the SOW. In 2023, October 7 is on a Saturday and October 8 is on a Sunday. Even though these two dates are immediately next to each other, they actually exist in different weeks. Oct 7 occurs on the last day of week 40 and Oct 8 occurs on the first day of week 41. Therefore, the "week" difference between these dates is 1, whereas the "week" difference between Oct 8 and Oct 14 is zero.
One way to arrive at the correctly calculated difference in weeks between two dates is to look at each date and determine the SOW date for each of those dates. So, in the prior example, given Sunday as the SOW, the SOW date for Oct 7 is Sunday, Oct 1 and the SOW date for Oct 8 is Sunday, Oct 8. Once the two SOW dates are determined, a final calculation for the difference in weeks is done. This works for any two dates and SOW values (Sunday, Monday, Tuesday, etc.).
Note that at least one other TDVT test case that specified "Monday" as the SOW was already passing without this fix, probably due either to coincidence or to a possible alternate matching canon. The fix provided in this PR should give the correct results for any SOW parameter and any two dates or datetimes.
Summary
Fix for TDVT datediff.sow.week tests. This was necessary to properly handle operations that find the difference in weeks between two dates when using a Start Of Week (SOW) parameter that is different from the default of "Sunday". For example, many countries in Europe and Asia use "Monday" as the first day of the week and some countries in Africa use "Saturday" as the first day of the week.
Detailed Explanation of Fix
The motivation for this fix is that it is sometimes not straightforward to determine which week a date falls in. For example, in the United States, Sunday is the SOW. In 2023, October 7 is on a Saturday and October 8 is on a Sunday. Even though these two dates are immediately next to each other, they actually exist in different weeks. Oct 7 occurs on the last day of week 40 and Oct 8 occurs on the first day of week 41. Therefore, the "week" difference between these dates is 1, whereas the "week" difference between Oct 8 and Oct 14 is zero. One way to arrive at the correctly calculated difference in weeks between two dates is to look at each date and determine the SOW date for each of those dates. So, in the prior example, given Sunday as the SOW, the SOW date for Oct 7 is Sunday, Oct 1 and the SOW date for Oct 8 is Sunday, Oct 8. Once the two SOW dates are determined, a final calculation for the difference in weeks is done. This works for any two dates and SOW values (Sunday, Monday, Tuesday, etc.).
TDVT Test Case Affected
DATEDIFF('week', DATETIME([date2]), [datetime0], 'monday')Note that at least one other TDVT test case that specified "Monday" as the SOW was already passing without this fix, probably due either to coincidence or to a possible alternate matching canon. The fix provided in this PR should give the correct results for any SOW parameter and any two dates or datetimes.
Test Results
test_results_BASELINE.csv test_results_WITHFIX_II-12235.csv