Forum Discussion
Need Help with SQL Query(first day and last day of each quarter in every year)
- 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.
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.
- srini040711Jan 31, 2024Copper ContributorHi JanZednicek, really thank you for your response and time and efforts