Forum Discussion
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 | Quarter End |
2021 | 2021 Q1 | 1/1/2021 | 3/31/2021 |
2021 | 2021 Q2 | 4/1/2021 | 6/30/2021 |
2021 | 2021 Q3 | 7/1/2021 | 9/31/2021 |
2021 | 2021 Q4 | 10/1/2021 | 12/31/2021 |
2022 | 2022 Q1 | 1/1/2022 | 3/31/2022 |
2022 | 2022 Q2 | 4/1/2022 | 6/30/2022 |
2022 | 2022 Q3 | 7/1/2022 | 9/31/2022 |
2022 | 2022 Q4 | 10/1/2022 | 12/31/2022 |
2023 | 2023 Q1 | 1/1/2023 | 3/31/2023 |
2023 | 2023 Q2 | 4/1/2023 | 6/30/2023 |
2023 | 2023 Q3 | 7/1/2023 | 9/31/2023 |
2023 | 2023 Q4 | 10/1/2023 | 12/31/2023 |
2024 | 2024 Q1 | 1/1/2024 | 3/31/2024 |
2024 | 2023 Q2 | 4/1/2024 | 6/30/2024 |
2024 | 2023 Q3 | 7/1/2024 | 9/31/2024 |
2024 | 2023 Q4 | 10/1/2024 | 12/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.
2 Replies
- JanZednicekCopper Contributor
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.- srini040711Copper ContributorHi JanZednicek, really thank you for your response and time and efforts