Presto SQL - Converting a date string to date format

Question

I'm on presto and have a date formatted as varchar that looks like -

7/14/2015 8:22:39 AM 

I've looked the presto docs and tried various things(cast, date_format, using split_part to parse and then cast) and am not getting this to convert to a date format that I can use with functions like date_diff.

I've tried:

cast(fieldname as timestamp) date_format(fieldname, '%Y-%m-%d %T) 

Both give me an error like this

'Value cannot be cast to timestamp: 3/31/2016 6:05:04 PM' 

How do I convert this?

Answer 1

I figured it out. The below works in converting it to a 24 hr date format.

select date_parse('7/22/2016 6:05:04 PM','%m/%d/%Y %h:%i:%s %p') 

See date_parse documentation in Presto.

Answer 2

You can also do something like this

date(cast('2016-03-22 15:19:34.0' as timestamp))