topic How get days of year with sql in databricks. in Data Engineering

How get days of year with sql in databricks.

Everton_Costa — Fri, 10 Mar 2023 21:19:39 GMT

Re: How get days of year with sql in databricks.

Cami — Fri, 10 Mar 2023 21:42:33 GMT

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}}')

Re: How get days of year with sql in databricks.

Everton_Costa — Mon, 13 Mar 2023 14:09:55 GMT

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