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.

Solution

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

Comments

[Jon Skeet]

I can't find documentation for all of this, but logically you want to parse to a datetime or timestamp or whatever, specifying the existing format, and then perform a second conversion from the datetime/timestamp back to a string with the desired format. So the first part is already working - I'd expect to see a date_format function or something similar