Hey moayyad,
Please refer below sample query.
SQL
DECLARE @startDate DATETIME
SET @startDate = '20140101'
DECLARE @endDate DATETIME
SET @endDate = '20140106'
;WITH dateRange AS
(
SELECT [Date] = DATEADD(dd, 1, @startDate)
WHERE DATEADD(dd, 1, @startDate) < = @endDate
UNION ALL
SELECT DATEADD(dd, 1, [Date])
FROM dateRange
WHERE DATEADD(dd, 1, [Date]) <= @endDate
)
SELECT [Date], DATENAME(dw,[Date]) [DayName]
FROM dateRange
Output
Date |
DayName |
2014-01-02 00:00:00.000 |
Thursday |
2014-01-03 00:00:00.000 |
Friday |
2014-01-04 00:00:00.000 |
Saturday |
2014-01-05 00:00:00.000 |
Sunday |
2014-01-06 00:00:00.000 |
Monday |