sql server - SQL Query to find out the non weekends (Monday-Friday) dates of a week from a variable date

Question

I am writing a SQL Query to find out the non weekends dates (i.e. Monday-Friday) of a week from a variable date.

Basically, it should show the dates which are not coming in weekends (Saturday & Sunday) of the week of a given date.

E.g. If the given date is 6th January, 2021. Then the output date should be between 4th-8th January, 2021. i.e the Week_Start_Date should be 2021-01-04 and Week_End_Date should be 2021-01-08.

I am writing below codes to find out the count of non-weekend days by referring a date and also writing a query to find out the Week_Start_Date & Week_End_Date using the referring date, but unable to combine both of these.

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '2021/01/04'
SET @EndDate = '2021/01/08'


SELECT
   (DATEDIFF(dd, @StartDate, @EndDate) + 1)
  -(DATEDIFF(wk, @StartDate, @EndDate) * 2)
  -(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)
  -(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)

  SELECT  DATEADD(DAY, 2 - DATEPART(WEEKDAY, GETDATE()), CAST(GETDATE() AS DATE)) [Week_Start_Date],  
DATEADD(DAY, 8 - DATEPART(WEEKDAY, GETDATE()), CAST(GETDATE() AS DATE)) [Week_End_Date] ;

EDIT:

I am getting the required output using below SQL query :-

SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0)
SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),4)

Answer 1

I am getting the required output using below SQL query :-

SELECT DATEADD(week,DATEDIFF(week,0,GETDATE()),0)
SELECT DATEADD(week,DATEDIFF(week,0,GETDATE()),4)