Convert abbreviated month name (MMM) to month number or to any date in SQL

Question

I have a table with the following column with string values

month
JAN
FEB

want to convert it to int or date to be able to get the latest month

I'm using bigquery

tried something like this - extract (MONTH from cast(CONCAT('2022-',month,'-01') result - Invalid date: '2022-JAN-01'

Answer 1

You can use the function PARSE_DATE to convert a string to a date and then FORMAT_DATE to extract the month from the date.

 PARSE_DATE("%Y-%b-%d",CONCAT('2022-',month,'-01') )

combined with

FORMAT_DATE("%m", date_value)

becomes

 FORMAT_DATE( "%m", PARSE_DATE("%Y-%b-%d",CONCAT('2022-',month,'-01') ))

explanation of date formats

  %b ou %h  3 letter name of month
  %d        Day of month as a number (01-31).
  %m        Month as a number (01-12).
  %Y        4 digit year as a number

Answer 2

You can use the PARSE_DATE function to achieve what you want:

PARSE_DATE('%Y-%b-%e', CONCAT('2022-',month,'-01'))

So, in full:

WITH months AS (
  SELECT 'JAN' as month UNION ALL
  SELECT 'FEB' as month UNION ALL
  SELECT 'MAR' as month UNION ALL
  SELECT 'APR' as month UNION ALL
  SELECT 'MAY' as month UNION ALL
  SELECT 'JUN' as month UNION ALL
  SELECT 'JUL' as month UNION ALL
  SELECT 'AUG' as month UNION ALL
  SELECT 'SEP' as month UNION ALL
  SELECT 'OCT' as month UNION ALL
  SELECT 'NOV' as month UNION ALL
  SELECT 'DEC' as month 
  )
SELECT 
  EXTRACT(MONTH FROM PARSE_DATE('%Y-%b-%e', CONCAT('2022-',month,'-01'))) as month_of_year
FROM months

This results to:

month_of_year
1
2
3
4
5
6
7
8
9
10
11
12