NOTE: I am running my query in Qubole's presto and sql command engine.
I am trying to convert my string timestamp to just date but none of the options are working out.
My string timestamp looks like 2017-03-29 10:32:28.0
and I want to have it like 2017-03-29
I have tried following queries to convert this string timestamp to retrieve date
1. select cast(created as date) from table1
Value cannot be cast to date: 2017-05-26 17:23:58.0
2. select cast(from_iso8601_timestamp(created) as date) from table1
Invalid format: "2014-12-19 06:06:36.0" is malformed at " 06:06:36.0"
3. select date(created) from table1
Value cannot be cast to date: 2012-10-24 13:50:00.0
How I can convert this timestamp to date in presto/sql?
GMB :
As far as explained in the documentation, prestoDB seems to expect timestamps in a format '2001-08-22 03:04:05.321', and dates in a '2001-08-22'.\n\nOne solution would be to use a string function to extract the relevant part of the string before converting it. We know that the date part is located before the first space in the string, so.\n\nIf you need the date part as a string datatype:\n\nsplit_part(created, ' ', 1)\n\n\nIf you need the date part as a date datatype:\n\ncast(split_part(created, ' ', 1) as date)\n",
2019-02-28T21:58:12