Amazon Athena - Convert String to Date

Question

I would like to convert some dates stored as a string to a date. The data looks as follows: 26APR2017:06:01:44. I would prefer it to be in any regular date format.

Can someone please help me to convert this to a date format? I'm sure the dateparse function is useful here and have used it before but am unsure how to adjust it for this particular data.

Answer 1

You can use date_parse() to parse and date() to cast timestamp retunred from parse function to date.

select date_parse('26APR2017:06:01:44','%d%b%Y:%H:%i:%s') will give you

2017-04-26 06:01:44.000

select date(date_parse('26APR2017:06:01:44','%d%b%Y:%H:%i:%s')) will give you

2017-04-26