Search code examples
ddlamazon-athena

How to convert string to date format in athena

I'm trying to convert the string to date format in athena. What formatting i need to use to convert it..

Date string i have in table: 10-Jul-2019 04:39:59

I'm using to_timestamp but it is not working

SELECT to_timestamp('10-Jul-2019 04:39:59', 'dd-J-yyyy HH:MM:SS') limit 1
Solution

  • You can use the DateTime type to better parse the string into a DateTime with Java syntax. https://prestodb.github.io/docs/current/functions/datetime.html

    If you also need to put the value into a timestamp, you can after the DateTime conversion, cast the result into a timestamp. Here the full statement:

    SELECT parse_datetime('10-Jul-2019 04:39:59', 'dd-MMM-yyyy hh:mm:ss') as timestamp