1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
--Declare Varibles
DECLARE @START date;
DECLARE @END date;
DECLARE @DATE date;
-- Date in format: yyyy-mm-dd
SET @START = '1985-12-09';
-- Date in format: yyyy-mm-dd
SET @END = '2021-07-12';
--Delete Temp Table if Exists
IF OBJECT_ID('tempdb..#days') IS NOT NULL
BEGIN
drop table #days;
END;
--Create Temp Table
create table #days ([date] datetime, 
[dayOfWeek] as datename(dw,[date]), 
[day] AS DAY([date]), 
[month] AS MONTH([date]),
[monthName] as datename(mm,[date]), 
[year] AS YEAR([date]),
[quarter] as datename(qq,[date]),
[weeknum] as datename(wk,[date]),
[dayOfYear] as datename(dy,[date]),
[dateString] AS CONVERT(nvarchar(20),[date],103) );
--SET Start Date equal to Data
SET @DATE = @START;
--Create Dates
while @DATE<=@END
BEGIN
insert into #days([date]) SELECT CONVERT(date,@DATE);
SELECT @DATE=DATEADD(dd,1,@DATE);
END
--SELECT Dates
SELECT COUNT(*) DaysOnPlanet FROM #days;
--Always good to clean up
drop table #days;