Presto SQL - Converting a date string to date format

sql presto
Moosa picture Moosa · Oct 5, 2016 · Viewed 112.4k times · Source

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

Moosa picture Moosa · Oct 5, 2016

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.

Related questions

Presto check if NULL and return default (NVL analog)

Is there any analog of NVL in Presto DB? I need to check if a field is NULL and return a default value. I solve this somehow like this: SELECT CASE WHEN my_field is null THEN 0 ELSE my_field …

Read More
Athena greater than condition in date column

I have the following query that I am trying to run on Athena. SELECT observation_date, COUNT(*) AS count FROM db.table_name WHERE observation_date > '2017-12-31' GROUP BY observation_date However it is producing this …

Read More
How to convert timestamp to date in Presto?

I like to convert my timestamp columns to date and time format. How should I write the query from presto? my timestamp is UTC time. Thank you very much Timestamp format"1506929478589" After query convert it looks like "2016-10-25 21:04:08.436"

Read More