binome74 opened a new pull request #10811: URL: https://github.com/apache/superset/pull/10811
### SUMMARY In MS SQL Server specifying SET DATEFIRST has no effect on DATEDIFF. [DATEDIFF always uses Sunday](https://docs.microsoft.com/en-us/sql/t-sql/functions/datediff-transact-sql?view=sql-server-2017#remarks) as the first day of the week to ensure the function operates in a deterministic way. This is not desirable for locales where weeks start on Monday. To get the first day of a calendar week for the current locale use DATEPART which does respect the DATEFIRST setting. ### TEST PLAN 1. Create a test table in SQL Server (any version). ``` SELECT cast(d AS DATE) d, n INTO p1w_test FROM (VALUES ('2020-08-01', 1), ('2020-08-02', 2), ('2020-08-03', 3), ('2020-08-04', 4), ('2020-08-05', 5), ('2020-08-06', 6) ,('2020-08-07', 7), ('2020-08-08', 8), ('2020-08-09', 9), ('2020-08-10', 10), ('2020-08-11', 11), ('2020-08-12', 12) ,('2020-08-13', 13), ('2020-08-14', 14), ('2020-08-15', 15), ('2020-08-16', 16), ('2020-08-17', 17), ('2020-08-18', 18) ,('2020-08-19', 19), ('2020-08-20', 20), ('2020-08-21', 21), ('2020-08-22', 22), ('2020-08-23', 23), ('2020-08-24', 24) ) T(d, n) ; ``` 2. Add p1w_test table as a source in Superset. 3. Explore the table, set "Time Column" to "d", "Time Grain" to "week". For the query pick SUM(n). 4. For North American locales (@@datefirst == 7) the resulting table should be ``` 2020-07-26 1 2020-08-02 35 2020-08-09 84 2020-08-16 133 2020-08-23 47 ``` 5. For European locales (@@datefirst == 1) the resulting table should be ``` 2020-07-27 3 2020-08-03 42 2020-08-10 91 2020-08-17 140 2020-08-24 24 ``` ### ADDITIONAL INFORMATION - [ ] Has associated issue: - [ ] Changes UI - [ ] Requires DB Migration. - [ ] Confirm DB Migration upgrade and downgrade tested. - [ ] Introduces new feature or API - [ ] Removes existing feature or API -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
