Calculations involving dates in database selections can cause headaches. For aggregation into “week buckets”, we were looking for a way to calculate the date of the first day of the week, given an arbitrary date and using the ISO 8601 definiton of a week.

On SAP HANA, we have some convenient standard functions at our disposal: ISOWEEK, WEEKDAY, ADD_DAYS. The ISOWEEK-function calculates the ISO week number for a given date. The WEEKDAY-function returns an integer for the day of the week of a given date: 0 for Monday, 6 for Sunday. In a sense, it follows the logic of ISO 8601, that says that Monday is the first day of the week.

A SELECT-statement calculating the date of the first day of the week for any given date is:
SELECT null AS DateFirst, TO_DATE('2017-01-01', 'YYYY-MM-DD') AS Date, ISOWEEK(TO_DATE('2017-01-01', 'YYYY-MM-DD')) AS IsoWeek, ADD_DAYS(TO_DATE('2017-01-01', 'YYYY-MM-DD'), 0 - WEEKDAY ( TO_DATE('2017-01-01', 'YYYY-MM-DD'))) AS IsoWeekFirstDay FROM DUMMY;

On Microsoft SQL Server, there are comparable, convenient standard functions: CAST, DATEPART, WEEKDAY. The DATEPART-function is versatile, and can be used to calculate the ISO 8601 week number from a given date.

SQL Server has a few features that complicate things. The WEEKDAY-function returns an integer value between 1 and 7 for a given date, representing the day of the week. Which day it is, depends on the value of an environment variable DATEFIRST. This is outside the realm of ISO 8601, but we need the functionality to get the date of the first day of th ISO week:
SELECT @@DATEFIRST AS DateFirst, '2017-01-01' AS Date, DATEPART(isowk, '2017-01-01') AS IsoWeek, CAST(CAST('2017-01-01' AS datetime) - DATEPART(WEEKDAY, CAST('2017-01-01' as datetime) + @@DATEFIRST + 5 ) % 7 AS date) AS IsoWeekFirstDay;