DATEADD( dd, 5 - DATEPART( dw, GETDATE()), GETDATE())
--===== Conditionally drop the test table to make reruns in SSMS easier. IF OBJECT_ID('tempdb..#TestTable','U') IS NOT NULL DROP TABLE #TestTable;GO--===== Populate the table with test data. -- This is not a part of the solution. SELECT TOP 100 DueDate = ABS(CHECKSUM(NEWID())) % DATEDIFF(dd,'2000','2020') + CAST('2000' AS DATETIME), PayDate = CAST(NULL AS DATETIME) INTO #TestTable FROM sys.all_columns ac1 CROSS JOIN sys.all_columns ac2; --===== This calculates the pay-on date for each date -- and stores it in the same table. -- This is a solution that doesn't depend on DATEFIRST. UPDATE #TestTable SET PayDate = DATEADD(dd,DATEDIFF(dd,4,DueDate+6)/7*7,4);--===== Display the results for verification. -- This is not a part of the solution. SELECT DueDate, DATENAME(dw,DueDate), PayDate, DATENAME(dw,PayDate) FROM #TestTable;