I have a table with the following column with string values

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'

CodePudding user response：

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

CodePudding user response：

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: