Finding week of the month from the date in databricks SQL

Question

I'm trying to get the week_of_the_month from the date in databricks sql

Here is my SQL:

with date as (
  select EXTRACT(DAY FROM '2017-01-01') as day
)

select case
       when day < 8 then '1'
       when day < 15 then '2'
       when day < 22 then '3'
       else '4'
       end as week_of_month

The above sql fails in some of the edge cases. For example 2010-03-31, then the week_of_month = 5

How do I find the week_of_month in databricks SQL?

Answer 1

The way you put it, you'd add yet another when clause

select case
         when day < 8 then '1'
         when day < 15 then '2'
         when day < 22 then '3'
         when day < 29 then '4'   --> new
         else '5'                 --> modified
       end as week_of_month