Amazon Athena Convert String to Date

Question

I am looking to convert the following string: mmm-dd-yyyy to a date: yyyy-mm-dd

e.g

Nov-06-2015 to 2015-11-06

within Amazon Athena

Have you looked into the [`date_parse`](https://prestodb.io/docs/0.172/functions/datetime.html) function? For example, try something like: `SELECT date_parse(d, '%b-%d-%Y')` — pault, Feb 20 '18 at 19:16

score 42 · Accepted Answer · edited Apr 22 '21 at 03:08

42

I would do date_parse. Adjust your regex accordingly.

select date_parse('Nov-06-2015','%b-%d-%Y')

2015-11-06 00:00:00.000

edited Apr 22 '21 at 03:08

eshirvana

answered Apr 05 '18 at 14:20

Blu3

score 6 · Answer 2 · edited Jun 22 '21 at 08:06

6

You can also use cast function to get desire output as date type.

select cast(date_parse('Nov-06-2015','%M-%d-%Y') as date);

output--2015-11-06

in amazon athena https://prestodb.io/docs/current/functions/datetime.html used date parse to parse string and cast to convert 2015-11-06 00:00:00.000 into 2015-11-06

edited Jun 22 '21 at 08:06

Janne Annala

answered Mar 23 '21 at 10:16

mohd Bilal

Are '%M' and '%b' equivalent for the `date_parse` pattern? – owengall Mar 22 '22 at 15:40

2 Answers2