How get days of year with sql in databricks.

[Cami · Accepted solution]

I hope it helps:

SELECT  DATEADD(DAY, rnk - 1, '{{StartDate}}')
FROM    (
    WITH lv0(c) AS(
    SELECT 1 as c
    UNION ALL
    SELECT 1
    )
    , lv1 AS (
    Select t1.c
    from lv0 t1
    cross JOIN lv0  t2
    )
     , lv2 AS (
    Select t1.c
    from lv1 t1
    cross JOIN lv1  t2
    )
    
    , lv3 AS (
    Select t1.c
    from lv2 t1
    cross JOIN lv2  t2
    )
     , lv4 AS (
    Select t1.c
    from lv3 t1
    cross JOIN lv3  t2
    )
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) rnk   FROM lv4
    ) tbl
WHERE   rnk - 1 <= DATEDIFF(DAY, '{{StartDate}}', '{{EndDate}}')

[Everton_Costa]

Thanks for the answer, could you explain the code to me or share a link that explains it please?