Generate Dates without loop (MSSQL, Oracle) by Hierarchical Queries

Generate numbers, days, weekdays, months, quarters.. (MSSQL like an Oracle Connect by Level option)

ORALCE / MSSQL

Generate numbers

oracle:
SELECT LEVEL FROM dual CONNECT BY LEVEL <= 10;
Mssql server:
WITH numbers(n)
AS (
SELECT 1
UNION ALL
SELECT n + 1 FROM numbers
WHERE n+1 <= 10
)
SELECT n FROM numbers;

Generate dates (2022):

WITH alldays(d)
AS (
SELECT 0
UNION ALL
SELECT d+1 FROM alldays
WHERE d+1 < DATEPART(DAYOFYEAR, DATEFROMPARTS(2022, 12, 31) )
)
SELECT
DATEADD(DAY, d, DATEFROMPARTS(2022, 1, 1))
FROM alldays
option (maxrecursion 0); — to avoid this error Msg 530, The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

Weekdays:

WITH alldays(d)
AS (
SELECT 0
UNION ALL
SELECT d+1 FROM alldays
WHERE d+1 < DATEPART(DAYOFYEAR, DATEFROMPARTS(2022, 12, 31) )
)
SELECT
DATEADD(DAY, d, DATEFROMPARTS(2022, 1, 1)) Monday,
DATEADD(DAY, d+4, DATEFROMPARTS(2022, 1, 1)) Friday
FROM alldays
WHERE DATENAME(WEEKDAY, DATEADD(DAY, d, DATEFROMPARTS(2022, 1, 1)) ) = ‘Monday’
option (maxrecursion 0);

Months

WITH alldays(m, months)
AS (
SELECT 1, DATENAME(MONTH, DATEFROMPARTS(2022, 1, 1) )
UNION ALL
SELECT m+1, DATENAME(MONTH, DATEFROMPARTS(2022, m+1, 1) ) FROM alldays
WHERE m+1 <= 12
)
SELECT
m, months,
DATEFROMPARTS(2022, m, 1) startday,
EOMONTH(DATEFROMPARTS(2022, m, 1)) endday
FROM alldays
option (maxrecursion 0);

Quarters

WITH alldays(q)
AS (
SELECT 0
UNION ALL
SELECT q+1 FROM alldays
WHERE q+1 < 4
)
SELECT
‘Quarter ‘ + CONVERT(VARCHAR(2), q+1) Quarter_,
DATEFROMPARTS(2022, q*3+1, 1) q_firstday,
EOMONTH( DATEFROMPARTS(2022, q*3+3, 1) ) q_endday
FROM alldays
option (maxrecursion 0);

please also visit: https://gist.github.com/iraklidd/7c40784d4f1477aa30e59b200ea265fe