String to YYYY-MM-DD date format in Athena

Question

So I've looked through documentation and previous answers on here, but can't seem to figure this out.

I have a STRING that represents a date. A normal output looks as such:

2018-09-19 17:47:12

If I do this, I get it to return in this format 2018-09-19 17:47:12.000:

SELECT
date_parse(click_time,'%Y-%m-%d %H:%i:%s') click_time
FROM
table.abc

But that's not the output I need. I was just trying to show that I'm close, but clearly missing something. When I change click_time to date_parse(click_time,'%Y-%m-%d'), it sends back INVALID_FUNCTION_ARGUMENT: Invalid format: "2018-09-19 17:47:12" is malformed at " 17:47:12"

So there's clearly something I'm not doing correctly to get it to simply return 2018-09-19.

Answer 1

date_parse converts a string to a timestamp. As per the documentation, date_parse does this:

date_parse(string, format) ? timestamp

It parses a string to a timestamp using the supplied format.

So for your use case, you need to do the following:

cast(date_parse(click_time,'%Y-%m-%d %H:%i:%s')) as date ) 

For your further reference, you can go to the below link for prestodb online documentation https://prestodb.github.io/docs/current/functions/datetime.html