find_season - BigQuery

SELECT `justfunctions.eu.find_season`("2020-01-04")
                                            
/*--Output--
Winter | 2019-12-01
*/

CREATE OR REPLACE FUNCTION `your_project_id.your_dataset_id.find_season`(`date` DATE) 
  RETURNS STRUCT<season STRING, start_of_season DATE> AS (CASE 
  WHEN EXTRACT(MONTH FROM date) IN (3, 4, 5) 
  THEN STRUCT("Spring" AS season, DATE(EXTRACT(YEAR FROM date), 3, 1) AS start_of_season)

  WHEN EXTRACT(MONTH FROM date) IN (6, 7, 8) 
  THEN STRUCT("Summer", DATE(EXTRACT(YEAR FROM date), 6, 1))

  WHEN EXTRACT(MONTH FROM date) IN (9, 10, 11) 
  THEN STRUCT("Autumn", DATE(EXTRACT(YEAR FROM date), 9, 1))

  WHEN EXTRACT(MONTH FROM date) = 12 
  THEN STRUCT("Winter", DATE(EXTRACT(YEAR FROM date), 12, 1))

  WHEN EXTRACT(MONTH FROM date) IN (1, 2) 
  THEN STRUCT("Winter", DATE(EXTRACT(YEAR FROM date) - 1, 12, 1))

  ELSE STRUCT("Unknown", NULL)
END
)
  OPTIONS ( description = '''Identifies the season name and the start date of the season for a given <date>.''')

SQL User Defined Function (SQL UDF)