Hi chetan,
Check the below query. Calculate start date and end date and insert into Temp table by checking sunday and pass temp table to select query where condition.
SQL
DECLARE @StartDate DATETIME, @EndDate DATETIME
SET @StartDate = '2020-04-18'
SET @EndDate = DATEADD(DAY,2,@StartDate)
CREATE TABLE #Test (ValidDate DATETIME)
WHILE @StartDate <= @EndDate
BEGIN
IF DATEPART(DW, @StartDate) = 1
BEGIN
SET @StartDate = DATEADD(DAY, 1, @StartDate)
SET @EndDate = DATEADD(DAY, 1, @EndDate)
END
ELSE
BEGIN
INSERT INTO #Test VALUES (@StartDate)
SET @StartDate = DATEADD(DAY, 1, @StartDate)
END
END
-- Pass the StartDate and EndDate to WHERE condition.
SELECT * FROM [TableName] WHERE [ColumnName] IN (SELECT ValidDate FROM #Test)
DROP TABLE #Test
