By default MS SQL Server has configured Sunday as the first day of a week. Unfortunately you’re unable to set the server config for the first day of a week in Salesforce Marketing Cloud, so you’ll need a SQL approach.
In the expression below, we add the specified number of weeks to the 0 date. What does that mean? Well, the date 0 represents midnight on Monday, 1 January 1900.
DATEADD(wk, FLOOR(DATEDIFF(day,0,current_timestamp)/7.0), 0)
DATEDIFF(day, 0, current_timestamp)will give the number of days since 1900-01-01 (which importantly was a Monday)
FLOOR(DATEDIFF(day, 0, current_timestamp)/7.0)Converts days since 1900–01–01 into weeks and rounds the output down. If we divide by '7' instead of '7.0' we can remove the
- Putting it all together, we add the (rounded down) total weeks since 1900–01–01 to the start date of 1900–01–01 which will output Monday.
- We can also rewrite the function slightly differently by choosing to add by days rather than weeks in the outer dateadd function. This requires us to multiply by 7 the result of the inner datediff.
DATEADD(dd, 7*(DATEDIFF(dd,0,current_timestamp)/7), 0)