- Newest
- Most votes
- Most comments
It is an expected output from date_parse which "parses timestamps"/"returns a timestamp" please follow this presto documentation : https://prestodb.io/docs/current/functions/datetime.html#mysql-date-functions.
You can use this query to truncate the time part from timestamp.
select Date(date_parse('2022-03-01','%Y-%m-%d'))
Also please note that Athena for DML queries uses presto for query execution. So in order to write a DML query upto Athena standards we have to use presto functions only.
Here date_parse() expects a string argument and parses that into timestamp format. Now as you stated that you also have null values. Here I have one question, are these empty values is in the form of "" or null in your table, Because if the empty values are in the form of 'null' then the date_parse should work perfectly fine. Like below:
select Date(date_parse(null,'%Y-%m-%d'))
But this will not :
select Date(date_parse('','%Y-%m-%d'))
because on seeing string '' it performs parsing only to find it is not in the standard format and throws error INVALID_FUNCTION_ARGUMENT: Invalid format: ""
In order to make it work you can use this query
select CASE date_g
WHEN '' THEN null
ELSE Date(date_parse(date_g,'%Y-%m-%d'))
END
FROM Table_name
Relevant content
- Accepted Answerasked 2 months ago
- AWS OFFICIALUpdated a month ago
- AWS OFFICIALUpdated 2 months ago
- AWS OFFICIALUpdated a year ago
- AWS OFFICIALUpdated a year ago