How to extract month name on a string datatype on athena

Question

SELECT sales_invoice_date,
       MONTH( DATE_TRUNC('month',
                         CASE
                           WHEN TRIM(sales_invoice_date) = '' THEN
                           DATE('1999-12-31')
                          ELSE
                           DATE_PARSE(sales_invoice_date, '%m/%d/%Y')
                         END) ) AS DT
  FROM testdata_parquet

I used the query above to convert the string into date and was able to get the month number on AWS athena but I wasn't able to get the corresponding month name

I have already tried monthname and datename('month', ...) but they gave the following error messages respectively:

SYNTAX_ERROR: line 2:1: Function monthname not registered

SYNTAX_ERROR: line 2:1: Function datename not registered

Answer 1

Athena is currently based on Presto .172, so you should refer to https://trino.io/docs/0.172/functions/datetime.html for available functions on date/time values.

You can get month name with date_format():

date_format(value, '%M')

or similarly format_datetime().

format_datetime(value, 'MMM')

Example:

presto:default> SELECT date_format(current_date, '%M');
  _col0
----------
 December
(1 row)

(verified on Presto 327, but will work in Athena too)

Answer 2

You can use to_char() function with 'month' argument :

to_char(sales_invoice_date, 'month')

in order to return the month names.