Get MONTH NAME from date in BigQuery SQL

Question

I'm trying to extract the MONTH NAME from a date in BigQuery, the type is DATE (i.e., 2019-09-19). I tried something like:

SELECT PARSE_DATE('%B',CAST(date_start AS string)) FROM table

but it doesn't work. I also tried some other stuff from the DATE funcs such as EXTRACT and the DATETIME funcs with no luck.

I know there are some functions like MONTHNAME() in other SQL languages, but they don't work in BQ.

Is there a way to do this that doesn't rely in CASE function?

Thanks.

Answer 1

As stated in the documentation you need to use the FORMAT_DATETIME function. The query would look as the following:

SELECT FORMAT_DATETIME("%B", DATETIME(<your_date_column_name>)) as month_name
FROM <your_table>

Here you'll find all the parameters you can use in order to display certain information about the date.

Answer 2

I'm trying to extract the MONTH NAME from a date in BigQuery, the type is DATE (i.e., 2019-09-19)

As your field is already of DATE type - You don't need to do any extra casting - just:

FORMAT_DATE('%B', date_start)    

will work - as in below example

#standardSQL
WITH `project.dataset.table` AS (
  SELECT DATE '2019-09-19' date_start
)
SELECT date_start, FORMAT_DATE('%B', date_start) AS month
FROM `project.dataset.table`   

with result

Row date_start  month    
1   2019-09-19  September