All Date Components in SQL

SET DATEFORMAT DMY;

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

;WITH Numbers AS (
    SELECT 0 AS Num
    UNION ALL
    SELECT Num + 1
    FROM Numbers
    WHERE Num + 1 <= DATEDIFF(DAY, @FromDateTime, @ToDateTime)
),
Dates AS (
    SELECT DATEADD(DAY, Num, @FromDateTime) AS DateValue
    FROM Numbers
)
SELECT 
    DateValue, 
    DATEADD(DAY, -DAY(DateValue) + 1, DateValue) AS MonthFirstDate,
    EOMONTH(DateValue) AS MonthLastDate,
    MONTH(DateValue) AS Month,
    DATENAME(MONTH, DateValue) AS MonthName,
    YEAR(DateValue) AS Year,
    CASE 
        WHEN MONTH(DateValue) >= 4 THEN CAST(YEAR(DateValue) AS VARCHAR) + RIGHT(CAST(YEAR(DateValue) + 1 AS VARCHAR), 2)
        ELSE CAST(YEAR(DateValue) - 1 AS VARCHAR) + RIGHT(CAST(YEAR(DateValue) AS VARCHAR), 2)
    END AS FinYear,
    FORMAT(DateValue, 'MMM-yyyy') AS MonthYear,
    MIN(DateValue) OVER() AS FromDate,
    MAX(DateValue) OVER() AS ToDate
FROM Dates
OPTION (MAXRECURSION 0);

CREATE FUNCTION dbo.FnGEN_GenerateDateSeries
(
    @FromDateTime DATE,
    @ToDateTime DATE
)
RETURNS TABLE
AS
RETURN
(
SET DATEFORMAT DMY;

WITH Numbers AS (
    SELECT 0 AS Num
    UNION ALL
    SELECT Num + 1
    FROM Numbers
    WHERE Num + 1 <= DATEDIFF(DAY, @FromDateTime, @ToDateTime)
),
Dates AS (
    SELECT DATEADD(DAY, Num, @FromDateTime) AS DateValue
    FROM Numbers
)
SELECT 
    DateValue, 
    DATEADD(DAY, -DAY(DateValue) + 1, DateValue) AS MonthFirstDate,
    EOMONTH(DateValue) AS MonthLastDate,
    MONTH(DateValue) AS Month,
    DATENAME(MONTH, DateValue) AS MonthName,
    YEAR(DateValue) AS Year,
    CASE 
        WHEN MONTH(DateValue) >= 4 THEN CAST(YEAR(DateValue) AS VARCHAR) + RIGHT(CAST(YEAR(DateValue) + 1 AS VARCHAR), 2)
        ELSE CAST(YEAR(DateValue) - 1 AS VARCHAR) + RIGHT(CAST(YEAR(DateValue) AS VARCHAR), 2)
    END AS FinYear,
    FORMAT(DateValue, 'MMM-yyyy') AS MonthYear,
    MIN(DateValue) OVER() AS FromDate,
    MAX(DateValue) OVER() AS ToDate
FROM Dates
OPTION (MAXRECURSION 0);

);
GO

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;

-- Generate the dates within the range
;WITH Numbers AS (
    SELECT 0 AS Num
    UNION ALL
    SELECT Num + 1
    FROM Numbers
    WHERE Num + 1 <= DATEDIFF(MONTH, @FromDateTime, @ToDateTime)
),
Dates AS (
    SELECT DATEADD(MONTH, Num, @FromDateTime) AS DateValue
    FROM Numbers
)
SELECT 
    DATEADD(DAY, -DAY(DateValue) + 1, DateValue) AS MonthFirstDate,
    EOMONTH(DateValue) AS MonthLastDate,
    MONTH(DateValue) AS Month,
    DATENAME(MONTH, DateValue) AS MonthName,
    YEAR(DateValue) AS Year,
    CASE 
        WHEN MONTH(DateValue) >= 4 THEN CAST(YEAR(DateValue) AS VARCHAR) + RIGHT(CAST(YEAR(DateValue) + 1 AS VARCHAR), 2)
        ELSE CAST(YEAR(DateValue) - 1 AS VARCHAR) + RIGHT(CAST(YEAR(DateValue) AS VARCHAR), 2)
    END AS FinYear,
    FORMAT(DateValue, 'MMM-yyyy') AS MonthYear,
    MIN(DateValue) OVER() AS FromDate,
    MAX(EOMONTH(DateValue)) OVER() AS ToDate
INTO #MonthDays
FROM Dates
OPTION (MAXRECURSION 0);

-- Display the result
SELECT * FROM #MonthDays;

-- Cleanup
DROP TABLE #MonthDays;

-- Create a table variable to store the generated dates
DECLARE @DateTable TABLE (
    DateID INT IDENTITY(1,1),
    MonthFirstDate DATE,
    MonthLastDate DATE,
    Month INT,
    MonthName NVARCHAR(20),
    Year INT,
    FinYear VARCHAR(10),
    MonthYear VARCHAR(10),
    FromDate DATE,
    ToDate DATE
);

;WITH Numbers AS (
    SELECT 0 AS Num
    UNION ALL
    SELECT Num + 1
    FROM Numbers
    WHERE Num + 1 <= DATEDIFF(MONTH, @FromDateTime, @ToDateTime)
),
Dates AS (
    SELECT DATEADD(MONTH, Num, @FromDateTime) AS DateValue
    FROM Numbers
)
INSERT INTO @DateTable (MonthFirstDate, MonthLastDate, Month, MonthName, Year, FinYear, MonthYear, FromDate, ToDate)
SELECT 
    DATEADD(DAY, -DAY(DateValue) + 1, DateValue) AS MonthFirstDate,
    EOMONTH(DateValue) AS MonthLastDate,
    MONTH(DateValue) AS Month,
    DATENAME(MONTH, DateValue) AS MonthName,
    YEAR(DateValue) AS Year,
    CASE 
        WHEN MONTH(DateValue) >= 4 THEN CAST(YEAR(DateValue) AS VARCHAR) + RIGHT(CAST(YEAR(DateValue) + 1 AS VARCHAR), 2)
        ELSE CAST(YEAR(DateValue) - 1 AS VARCHAR) + RIGHT(CAST(YEAR(DateValue) AS VARCHAR), 2)
    END AS FinYear,
    FORMAT(DateValue, 'MMM-yyyy') AS MonthYear,
    @FromDateTime AS FromDate,
    @ToDateTime AS ToDate
FROM Dates
OPTION (MAXRECURSION 0);

-- Display the result
SELECT * FROM @DateTable;