Dynamic Date Series in SQL Server (Financial Year + Calendar Year Handling)

SET DATEFORMAT dmy;

DECLARE @FinYear INT = 202324; -- Example financial year format (YYYYYY)
DECLARE @YearType INT = 1; -- Example: 1 for Financial Year, 2 for Calendar Year(2023), 3 for Calendar Year(2024)
DECLARE @Year INT;

-- Determine the year based on the YearType
IF @YearType IN (1, 2)  
    SET @Year = LEFT(@FinYear, 4);  
IF @YearType = 3  
    SET @Year = LEFT(@FinYear, 4) + 1;

-- Initialize date range
DECLARE @FromDateTime DATE;
DECLARE @ToDateTime DATE;

IF @YearType = 1
BEGIN
    -- Financial Year: April 1 to March 31
    SET @FromDateTime = DATEFROMPARTS(@Year, 4, 1); 
    SET @ToDateTime = DATEFROMPARTS(@Year + 1, 3, 31);
END
IF @YearType = 2
BEGIN
    -- Calendar Year: January 1 to December 31
    SET @FromDateTime = DATEFROMPARTS(@Year, 1, 1); 
    SET @ToDateTime = DATEFROMPARTS(@Year, 12, 31);
END
IF @YearType = 3
BEGIN
    -- Custom Year: January 1 to December 31
    SET @FromDateTime = DATEFROMPARTS(@Year, 1, 1); 
    SET @ToDateTime = DATEFROMPARTS(@Year, 12, 31);
END;

SET NOCOUNT ON;
SET DATEFORMAT DMY;

--If Datetime Given

DECLARE @FromDateTime DATE = '01/04/2023';
DECLARE @ToDateTime   DATE = '31/03/2025';


IF OBJECT_ID('tempdb..#DateSeries') IS NOT NULL
    DROP TABLE #DateSeries;


CREATE TABLE #DateSeries
(
    DateID          INT             NOT NULL IDENTITY(1,1),
    DateValue       DATE            NOT NULL,
    MonthFirstDate  DATE            NOT NULL,
    MonthLastDate   DATE            NOT NULL,
    MonthNum        TINYINT         NOT NULL,   -- 1-12 fits in TINYINT (0-255)
    MonthName       NVARCHAR(10)    NOT NULL,
    YearNum         SMALLINT        NOT NULL,   -- year fits in SMALLINT (up to 32767)
    FinYear         VARCHAR(6)      NOT NULL,   -- e.g. 202324
    MonthYear       VARCHAR(10)     NOT NULL,   -- e.g. Apr-2023
    FromDate        DATE            NOT NULL,
    ToDate          DATE            NOT NULL
);


;WITH
L0 AS (SELECT 1 AS c UNION ALL SELECT 1),           -- 2 rows
L1 AS (SELECT 1 AS c FROM L0 A CROSS JOIN L0 B),    -- 4 rows
L2 AS (SELECT 1 AS c FROM L1 A CROSS JOIN L1 B),    -- 16 rows
L3 AS (SELECT 1 AS c FROM L2 A CROSS JOIN L2 B),    -- 256 rows
L4 AS (SELECT 1 AS c FROM L3 A CROSS JOIN L3 B),    -- 65,536 rows
Tally AS
(
    -- Row number 0 se start (pehli date = @FromDateTime + 0 days)
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS Num 
    FROM L4
),
Dates AS
(
    SELECT DATEADD(DAY, Num, @FromDateTime) AS DateValue
    FROM Tally
    WHERE Num <= DATEDIFF(DAY, @FromDateTime, @ToDateTime)
)
INSERT INTO #DateSeries
(
    DateValue, MonthFirstDate, MonthLastDate,
    MonthNum, MonthName, YearNum,
    FinYear, MonthYear, FromDate, ToDate
)
SELECT
    DateValue,
    DATEADD(DAY, 1 - DAY(DateValue), DateValue)                         AS MonthFirstDate,
    EOMONTH(DateValue)                                                   AS MonthLastDate,
    MONTH(DateValue)                                                     AS MonthNum,
    DATENAME(MONTH, DateValue)                                           AS MonthName,
    YEAR(DateValue)                                                      AS YearNum,
    CASE
        WHEN MONTH(DateValue) >= 4
        THEN CAST(YEAR(DateValue)     AS VARCHAR(4)) 
           + RIGHT(CAST(YEAR(DateValue) + 1 AS VARCHAR(4)), 2)  -- e.g. 202324
        ELSE CAST(YEAR(DateValue) - 1 AS VARCHAR(4)) 
           + RIGHT(CAST(YEAR(DateValue)     AS VARCHAR(4)), 2)  -- e.g. 202223
    END                                                                  AS FinYear,
    LEFT(DATENAME(MONTH, DateValue), 3) + '-' 
    + CAST(YEAR(DateValue) AS VARCHAR(4))                                AS MonthYear,
    @FromDateTime                                                        AS FromDate,
    @ToDateTime                                                          AS ToDate

FROM Dates;

ALTER TABLE #DateSeries
    ADD CONSTRAINT PK_DateSeries PRIMARY KEY CLUSTERED (DateID);  -- ← ADD

CREATE NONCLUSTERED INDEX NIX_DateSeries_Date
    ON #DateSeries (DateValue ASC);


CREATE NONCLUSTERED INDEX NIX_DateSeries_FinYear
    ON #DateSeries (FinYear, MonthNum)
    INCLUDE (MonthFirstDate, MonthLastDate, MonthYear);

CREATE NONCLUSTERED INDEX NIX_DateSeries_YearMonth
    ON #DateSeries (YearNum, MonthNum)
    INCLUDE (MonthFirstDate, MonthLastDate);
-----------------------Output-----------------
SELECT
    *
FROM #DateSeries
ORDER BY DateValue;

--Advance 

SET NOCOUNT ON;
SET DATEFORMAT DMY;

DECLARE @FromDateTime DATE = '01/04/2023';
DECLARE @ToDateTime   DATE = '31/03/2025';

-- Safe drop
IF OBJECT_ID('tempdb..#DateSeries') IS NOT NULL
    DROP TABLE #DateSeries;

-- ============================================================
-- Tally + SELECT INTO (no pre-CREATE TABLE needed)
-- IDENTITY column = ROW_NUMBER() via IDENTITY(INT,1,1)
-- ============================================================
;WITH
L0 AS (SELECT 1 AS c UNION ALL SELECT 1),
L1 AS (SELECT 1 AS c FROM L0 A CROSS JOIN L0 B),
L2 AS (SELECT 1 AS c FROM L1 A CROSS JOIN L1 B),
L3 AS (SELECT 1 AS c FROM L2 A CROSS JOIN L2 B),
L4 AS (SELECT 1 AS c FROM L3 A CROSS JOIN L3 B),
Tally AS
(
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS Num
    FROM L4
),
Dates AS
(
    SELECT DATEADD(DAY, Num, @FromDateTime) AS DateValue
    FROM Tally
    WHERE Num <= DATEDIFF(DAY, @FromDateTime, @ToDateTime)
),
Base AS
(
    SELECT
        DateValue,
        DAY(DateValue)                                              AS DayNum,
        MONTH(DateValue)                                            AS MonthNum,
        YEAR(DateValue)                                             AS YearNum,
        ((DATEPART(WEEKDAY, DateValue) + @@DATEFIRST - 2) % 7) + 1 AS DayOfWeek,
        CASE
            WHEN MONTH(DateValue) >= 4 THEN MONTH(DateValue) - 3
            ELSE MONTH(DateValue) + 9
        END                                                         AS FinMonthNum
    FROM Dates
)
SELECT
    -- IDENTITY column (SELECT INTO ke saath kaam karta hai)
    IDENTITY(INT, 1, 1)                                             AS DateID,

    -- Core
    B.DateValue,
    CAST(@FromDateTime AS DATE)                                     AS FromDate,
    CAST(@ToDateTime   AS DATE)                                     AS ToDate,

    -- Day
    CAST(B.DayNum AS TINYINT)                                       AS DayNum,
    DATENAME(WEEKDAY, B.DateValue)                                  AS DayName,
    CAST(B.DayOfWeek AS TINYINT)                                    AS DayOfWeek,
    CAST(DATEPART(DAYOFYEAR, B.DateValue) AS SMALLINT)              AS DayOfYear,

    -- Week
    CAST(DATEPART(ISO_WEEK, B.DateValue) AS TINYINT)                AS WeekNumber,

    -- Month
    CAST(B.MonthNum AS TINYINT)                                     AS MonthNum,
    DATENAME(MONTH, B.DateValue)                                    AS MonthName,
    CAST(DATEADD(DAY, 1 - B.DayNum, B.DateValue) AS DATE)          AS MonthFirstDate,
    CAST(EOMONTH(B.DateValue) AS DATE)                              AS MonthLastDate,
    LEFT(DATENAME(MONTH, B.DateValue), 3) + '-'
        + CAST(B.YearNum AS VARCHAR(4))                             AS MonthYear,

    -- Calendar Quarter
    CAST(DATEPART(QUARTER, B.DateValue) AS TINYINT)                 AS QuarterNum,
    'Q' + CAST(DATEPART(QUARTER, B.DateValue) AS VARCHAR(1))        AS QuarterName,

    -- Calendar Year
    CAST(B.YearNum AS SMALLINT)                                     AS YearNum,

    -- Financial Month / Quarter / Year
    CAST(B.FinMonthNum AS TINYINT)                                  AS FinMonthNum,
    CAST(((B.FinMonthNum - 1) / 3) + 1 AS TINYINT)                 AS FinQuarterNum,
    'FQ' + CAST(((B.FinMonthNum - 1) / 3) + 1 AS VARCHAR(1))       AS FinQuarterName,
    CASE
        WHEN B.MonthNum >= 4
        THEN CAST(B.YearNum     AS VARCHAR(4))
           + RIGHT(CAST(B.YearNum + 1 AS VARCHAR(4)), 2)
        ELSE CAST(B.YearNum - 1 AS VARCHAR(4))
           + RIGHT(CAST(B.YearNum     AS VARCHAR(4)), 2)
    END                                                             AS FinYear,

    -- Flags (BIT)
    CAST(CASE WHEN B.DayOfWeek IN (6,7) THEN 1 ELSE 0 END AS BIT) AS IsWeekend,
    CAST(CASE WHEN B.DayOfWeek IN (6,7) THEN 0 ELSE 1 END AS BIT) AS IsWeekday,
    CAST(CASE WHEN B.DayNum = 1         THEN 1 ELSE 0 END AS BIT) AS IsMonthStart,
    CAST(CASE WHEN B.DateValue = EOMONTH(B.DateValue)
                                        THEN 1 ELSE 0 END AS BIT) AS IsMonthEnd,
    CAST(CASE WHEN B.MonthNum = 4 AND B.DayNum = 1
                                        THEN 1 ELSE 0 END AS BIT) AS IsFinYearStart,
    CAST(CASE WHEN B.MonthNum = 3
               AND B.DateValue = EOMONTH(B.DateValue)
                                        THEN 1 ELSE 0 END AS BIT) AS IsFinYearEnd,
    CAST(CASE WHEN DAY(EOMONTH(DATEFROMPARTS(B.YearNum, 2, 1))) = 29
                                        THEN 1 ELSE 0 END AS BIT) AS IsLeapYear

INTO #DateSeries          -- <-- Table yahan auto-create hoti hai
FROM Base B
ORDER BY B.DateValue;     -- INSERT order guaranteed

-- ============================================================
-- PK + Indexes (INSERT ke BAAD — golden rule)
-- ============================================================

ALTER TABLE #DateSeries
    ADD CONSTRAINT PK_DateSeries PRIMARY KEY CLUSTERED (DateID);

CREATE UNIQUE NONCLUSTERED INDEX UIX_DateSeries_Date
    ON #DateSeries (DateValue);

CREATE NONCLUSTERED INDEX NIX_DateSeries_FinYear
    ON #DateSeries (FinYear, FinQuarterNum, FinMonthNum)
    INCLUDE (MonthFirstDate, MonthLastDate, MonthYear, FinQuarterName);

CREATE NONCLUSTERED INDEX NIX_DateSeries_CalYear
    ON #DateSeries (YearNum, QuarterNum, MonthNum)
    INCLUDE (MonthFirstDate, MonthLastDate, MonthYear, QuarterName);

CREATE NONCLUSTERED INDEX NIX_DateSeries_Flags
    ON #DateSeries (IsWeekday, IsWeekend, DateValue)
    INCLUDE (DayName, WeekNumber);

-- ============================================================
-- Result
-- ============================================================
SELECT * FROM #DateSeries ORDER BY DateValue;