topic Convert Week of Year to Month in SQL? in Data Engineering

Convert Week of Year to Month in SQL?

ST — Wed, 02 Feb 2022 16:25:58 GMT

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!

Re: Convert Week of Year to Month in SQL?

Hubert-Dudek — Wed, 02 Feb 2022 19:41:58 GMT

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
 """)