Convert Week of Year to Month in SQL?

[ST]

Hi all,

Was wondering if there was any built in function or code that I could utilize to convert a singular week of year integer (i.e. 1 to 52), into a value representing month (i.e. 1-12)?

The assumption is that a week start on a Monday and end on a Sunday.

Thanks!

[Hubert-Dudek · Accepted solution]

we need old parser as new doesn't support weeks. Than we can map what we need using w - year of year and u - first day of the week:

spark.sql("set spark.sql.legacy.timeParserPolicy=LEGACY")
spark.sql("""
SELECT 
    extract(
        month from to_date("2022-12-1", "y-w-u")
     ) as month_of_year
 """)

[Kaniz]

Hi @Stanley Tsang​ , Does @Hubert Dudek​ 's solution work for you?