Presto SQL - Converting a date string to date format

46
votes

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?

8
sqlpresto
I think problem is with date format. Can you change the format like this 2016-03-31 6:05:04 PM and cast it ? - Pரதீப்
You should try the ISO 8601 format "2016-03-31 18:05:04". I'm almost certain it should work - coladict
@coladict i tried select cast('2016-03-31 6:05:04 PM' as timestamp) and this doesnt work either - Moosa
Not 6:05:04 PM, it has to be 06:05:04 for AM and 18:05:04 for PM. - coladict
@coladict that might be a formatting issue with the data but that leading 0 is what the cast should fix. But even when i add the leading 0 to the above example, i still get the same error. - Moosa

8 Answers

56
votes

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.

3
votes

Use: cast(date_parse(inv.date_created,'%Y-%m-%d %h24:%i:%s') as date)

Input: String timestamp

Output: date format 'yyyy-mm-dd'

3
votes

You can also do something like this

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

2
votes

Converted DateID having date in Int format to date format: Presto Query

Select CAST(date_format(date_parse(cast(dateid as varchar(10)), '%Y%m%d'), '%Y/%m-%d') AS DATE)
from
     Table_Name
limit 10;
1
votes
    select date_format(date_parse(t.payDate,'%Y-%m-%d %H:%i:%S'),'%Y-%m-%d') as payDate 
    from testTable  t 
    where t.paydate is not null and t.paydate <> '';
1
votes

If your string is in ISO 8601 format, you can also use from_iso8601_timestamp

0
votes

SQL 2003 standard defines the format as follows:

<unquoted timestamp string> ::= <unquoted date string> <space> <unquoted time string>
<date value> ::= <years value> <minus sign> <months value> <minus sign> <days value>
<time value> ::= <hours value> <colon> <minutes value> <colon> <seconds value>

There are some definitions in between that just link back to these, but in short YYYY-MM-DD HH:MM:SS with optional .mmm milliseconds is required to work on all SQL databases.

0
votes

date_format requires first argument as timestamp so not the best way to convert a string. Use date_parse instead.

Also, use %c for non zero-padded month, %e for non zero-padded day of the month and %Y for four digit year.

SELECT date_parse('7/22/2016 6:05:04 PM', '%c/%e/%Y %r')