How to get all dates between two Dates in SQL

 

How to get all dates between two Dates:

SELECT * FROM (SELECT TOP (DATEDIFF(DAY, '01-Jan-2020', '10-Jan-2020') + 1) CurrentDate = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY A.object_id) - 1, '01-Jan-2020') FROM sys.all_objects A) AS B

Output:

How to get yearly numbers of days between two Dates:

SELECT LeaveYear =year(CurrentDate), NoOfDays=count(year(CurrentDate)) FROM (SELECT TOP (DATEDIFF(DAY, '01-Jan-2019', '31-Dec-2021') + 1) CurrentDate = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY A.object_id) - 1, '01-Jan-2019') FROM sys.all_objects A) AS B group by year(CurrentDate)

Output: