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?

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ரதீப், Oct 05 '16 at 17:56
You should try the ISO 8601 format "2016-03-31 18:05:04". I'm almost certain it should work — coladict, Oct 05 '16 at 17:59
@coladict i tried `select cast('2016-03-31 6:05:04 PM' as timestamp)` and this doesnt work either — Moosa, Oct 05 '16 at 18:10
Not `6:05:04 PM`, it has to be `06:05:04` for AM and `18:05:04` for PM. — coladict, Oct 05 '16 at 18:16
@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, Oct 05 '16 at 18:24
did you try an ANSI SQL timestamp literal? `timestamp '2016-03-31 18:05:04'` — , Oct 05 '16 at 20:31

score 73 · Answer 1 · edited Jun 10 '19 at 07:40

73

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.

edited Jun 10 '19 at 07:40

Piotr Findeisen

19,480
2
52
82

answered Oct 05 '16 at 19:38

Moosa

3,126
5
25
45

2

Just be aware that if you want a date from this you have to explicitly cast it back to date (from timestamp) - just using a format like '%Y-%m-%d' is not enough... – Tom Wagstaff Feb 19 '20 at 14:58

score 17 · Answer 2 · answered Mar 06 '20 at 00:35

17

You can also do something like this

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

answered Mar 06 '20 at 00:35

Russell Lego

440
5
6

score 5 · Answer 3 · edited Feb 11 '19 at 23:21

5

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

Input: String timestamp

Output: date format 'yyyy-mm-dd'

edited Feb 11 '19 at 23:21

Cody Gray - on strike

239,200
50
490
574

answered Feb 11 '19 at 22:53

VINOD KUMAR KEDARNATH

51
1
1

score 4 · Answer 4 · edited Nov 27 '18 at 19:42

4

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;

edited Nov 27 '18 at 19:42

EoinS

5,405
1
19
32

answered Jun 17 '18 at 07:02

Rajiv Singh

958
1
9
14

score 3 · Answer 5 · edited May 10 '19 at 05:15

3

    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 <> '';

edited May 10 '19 at 05:15

belwood

3,320
11
38
45

answered May 10 '19 at 02:04

Iamnotme

31
2

score 3 · Answer 6 · answered Sep 27 '19 at 21:40

3

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

answered Sep 27 '19 at 21:40

skeller88

4,276
1
32
34

score 1 · Answer 7 · answered Aug 21 '20 at 20:38

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')

score 0 · Answer 8 · answered Oct 05 '16 at 18:12

0

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.

answered Oct 05 '16 at 18:12

coladict

4,799
1
16
27

yeah but it's got something to do with the AM/PM. If I do `select cast('2016-03-31 6:05:04 PM' as timestamp)` it doesnt work. but if i remove am/pm and do `select cast('2016-03-31 6:05:04' as timestamp)` then it works. but i need the am/pm or convert to 24 hour. – Moosa Oct 05 '16 at 18:15
If you insist on using the non-standard `3/31/2016 6:05:04 PM` according to the documentation, the format is `%c/%e/%y %r`. Have a look https://prestodb.io/docs/current/functions/datetime.html – coladict Oct 05 '16 at 18:26
that's how my data is formatted. i'm willing to reformat but dont know how. that's my question. per the comment above, if i do `date_format('7/22/2016 6:05:04 PM', '%c/%e/%y %r')` that gives me an error too – Moosa Oct 05 '16 at 18:54

score 0 · Answer 9 · answered Jun 22 '23 at 11:35

0

In case you need to convert from timestamp to date, you can also date() function, just like mySQL:

select date(your_date_colunm_name) as new_date_colunm

answered Jun 22 '23 at 11:35

Maria Clara Bezerra

509
3
5

Presto SQL - Converting a date string to date format

9 Answers9

Linked