CODEWITHSUNDEEP
sqlprestoamazon-athena
Ray
Ray

Reputation: 143

How to extract month name on a string datatype on athena

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

Upvotes: 10

Views: 29220

Answers (3)

Sohail Anjum
Sohail Anjum

Reputation: 94

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

Upvotes: 0

Piotr Findeisen
Piotr Findeisen

Reputation: 20710

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)

Upvotes: 11

Barbaros Özhan
Barbaros Özhan

Reputation: 65113

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

to_char(sales_invoice_date, 'month')

in order to return the month names.

Upvotes: 1

Related Questions