Presto SQL - Convert Date Format to Date Format

Question

I am on presto and I have a date in varchar format that looks like -

7/14/2015 8:22:39 AM 

I looked through the docs before and tried various things (cast, date_format, using split_part to parse and then cast) and don't get it to convert to a date format, which I can use with functions like date_diff.

I tried:

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

Both give me such an error

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

How do i convert this?

Answer 1

I understood. The following is converting it to a 24-hour date format.

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

See the documentation for date_parse at Presto .

Answer 2

Converted DateID with Int date 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; 

Answer 3

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

Input: String timestamp

Output: date format 'yyyY-mm-dd'

Answer 4

The SQL 2003 standard defines a 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 several definitions between them that simply reference them, but in short, YYYY-MM-DD HH:MM:SS with an additional .mmm milliseconds is required to work with all SQL databases.

Answer 5

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

Answer 6

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

Answer 7

try the following:

 SELECT convert(datetime, '7/14/2015 8:22:39 AM', 101) 

output:

2015-07-14 08: 22: 39.000