Forum Discussion

srini040711's avatar
srini040711
Copper Contributor
Jan 31, 2024
Solved

Need Help with SQL Query(first day and last day of each quarter in every year)

Hi Everyone,    Could someone help me with SQL query to get the result like below(first day and last day of each quarter in every year)   Thanks in Advance. Year Year-Quarter Quarter Start ...
  • JanZednicek's avatar
    Jan 31, 2024

    Hi hope you are doing well, see the script below

    DECLARE @StartDate DATE = '2021-01-01';
    DECLARE @EndDate DATE = '2024-12-31';

    WITH DateSequence AS (
    SELECT @StartDate AS Date
    UNION ALL
    SELECT DATEADD(DAY, 1, Date)
    FROM DateSequence
    WHERE Date < @EndDate
    )
    SELECT
    CONCAT(YEAR(Date),' Q',DATEPART(QUARTER, Date)) AS [Year - Quarter]
    ,MIN(Date) AS [Quarter Start]
    ,MAX(Date) AS [Quarter End]
    FROM DateSequence
    GROUP BY CONCAT(YEAR(Date),' Q',DATEPART(QUARTER, Date))
    ORDER BY MIN(Date)
    OPTION (MAXRECURSION 0);

    It uses recursive query as described here: https://janzednicek.cz/en/sql-loop-do-you-know-how-to-use-while-or-recursive-cte/. Maybe I would recommend to create a calendar (also in the article) in case you need to access information like this repeatedly.

Resources