Creating Calendar in SQL Server

In the below example, I have created a simple calendar using SQL Server and its predefined functions

SQL Server Temporary Tables

The temporary tables are useful for storing the immediate result sets that are accessed multiple times.

Creating temporary tables

SQL Server provided two ways to create temporary tables: SELECT INTO and CREATE TABLE statements.

CREATE OR ALTER PROCEDURE calender(@start_date DATE, @end_date DATE)
AS
BEGIN
    DECLARE @DateDiff INT;
    DECLARE @count INT;
    SET @count = 0; -- DAY ONE COUNT OF START DATE
    SET @DateDiff = DATEDIFF(DAY, @start_date, @end_date); -- DIFF BTW TWO DATE
    DROP TABLE IF EXISTS #temp_cal;
    CREATE TABLE #temp_cal(
        id INT IDENTITY(1,1) PRIMARY KEY,
        d_date DATE,
        end_date DATE
    );
    WHILE @count <= @DateDiff BEGIN
        INSERT INTO #temp_cal(d_date, end_date) VALUES(DATEADD(DAY, @count, @start_date), @end_date);
        SET @count = @count + 1;
    END;
    SELECT DAY(d_date) AS 'DAY',
           MONTH(d_date) AS 'MONTH',
           DATENAME(weekday, d_date) AS 'DAY_NAME',
           DATENAME(month, d_date) AS 'MONTH_NAME',
           DATENAME(year, d_date) AS 'YEAR',
           d_date AS 'DATE',
           DATENAME(week, d_date) AS 'WEEK',
           DATEPART(DY, d_date) AS 'DAY_OF_YEAR',
           DATEPART(ISO_WEEK, d_date) AS 'ISO_WEEK',
           DATEPART(wk, d_date) AS 'US WEEK',
           DATEPART(wk, d_date) AS 'WEEK_OF_YEAR',
           DATEDIFF(DAY,d_date, end_date) AS 'NUMBER_OF_DAYS_IN_MONTH',
           DATEDIFF(WEEK,d_date,end_date) AS 'WEEKS_IN_YEAR',
           DATEDIFF(MONTH,d_date,end_date) AS 'MONTHS_IN_YEAR',
           FORMAT(d_date, 'D', 'en-US' ) AS 'CULTURES_FOR_US'
    FROM #temp_cal;
END;
EXEC calender @start_date = '2023-01-01', @end_date = '2023-12-31';

SQL

Copy