SQL DATEPART(qq, @date) date quarter start October

I have a query that prints the data grouped quarter, the query uses datepart(qq,repo.lesson_date) SQL function.

My issue is I want my first quarter to be October-December and not Jan-March like the output I get from the SQL function above.

Is there anyone can assist?

Answer

You may use an additional calculation (DATEPART(qq, repo.lesson_date) % 4 + 1) as a possible option.

Example:

SELECT 
   DATEPART(qq, lesson_date) AS [Quarter], 
   DATEPART(qq, lesson_date) % 4 + 1 AS [NewQuarter]
FROM (VALUES
   ('20210101'),
   ('20210401'),
   ('20210701'),
   ('20211001')
) repo (lesson_date)

Result:

Quarter NewQuarter
1       2
2       3
3       4
4       1