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?

question from:https://stackoverflow.com/questions/39880540/presto-sql-converting-a-date-string-to-date-format

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.