SQL Server — Find Monday of the Current Week

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 FLOOR() function.
• 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)