9
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

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
Ray
  • 133
  • 1
  • 1
  • 7

3 Answers3

10

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)

Piotr Findeisen
  • 19,480
  • 2
  • 52
  • 82
1

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

to_char(sales_invoice_date, 'month')

in order to return the month names.

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
0

SELECT date_format(current_date, 'MMMM') shows me February SELECT date_format(current_date, 'MMM') Feb

However, SELECT date_format(current_date, '%M') is not working for me, its only giving me %2 only -- for February

Sohail Anjum
  • 84
  • 7