Select all days between two dates with Day Name in SQL Server

Last Reply 6 months ago By pandeyism

Posted 6 months ago

hi all ;

please i want to get all days between 2 dates with day name 

i have find this 2 codes but i cant to merge it to one code 

DECLARE @MinDate DATE = '20140101',
        @MaxDate DATE = '20140106';

SELECT  TOP (DATEDIFF(DAY, @MinDate, @MaxDate) + 1)
        Date = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, @MinDate)
FROM    sys.all_objects a
        CROSS JOIN sys.all_objects b;
        
     
SELECT DATENAME(dw,'12/21/2018')

 

Posted 6 months ago

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