Presto SQL - 将日期字符串转换为日期格式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/39880540/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Presto SQL - Converting a date string to date format
提问by Moosa
I'm on presto and have a date formatted as varchar that looks like -
我在 presto 并且有一个日期格式为 varchar 看起来像 -
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.
我查看了 presto 文档并尝试了各种方法（转换、date_format、使用 split_part 解析然后转换）并且没有将其转换为我可以与 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?
我该如何转换？
回答by Moosa
I figured it out. The below works in converting it to a 24 hr date format.
我想到了。下面将其转换为 24 小时日期格式。
select date_parse('7/22/2016 6:05:04 PM','%m/%d/%Y %h:%i:%s %p')
回答by Rajiv Singh
Converted DateID having date in Int format to date format: Presto Query
将具有 Int 格式日期的 DateID 转换为日期格式：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;
回答by VINOD KUMAR KEDARNATH
Use: cast(date_parse(inv.date_created,'%Y-%m-%d %h24:%i:%s') as date)
用： cast(date_parse(inv.date_created,'%Y-%m-%d %h24:%i:%s') as date)
Input: String timestamp
输入： String timestamp
Output: date format 'yyyy-mm-dd'
输出： date format 'yyyy-mm-dd'
回答by Iamnotme
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 <> '';
回答by skeller88
If your string is in ISO 8601 format, you can also use from_iso8601_timestamp
如果您的字符串是 ISO 8601 格式，您还可以使用from_iso8601_timestamp
回答by Russell Lego
You can also do something like this
你也可以做这样的事情
date(cast('2016-03-22 15:19:34.0' as timestamp))
date(cast('2016-03-22 15:19:34.0' 作为时间戳))
回答by coladict
SQL 2003 standard defines the format as follows:
SQL 2003 标准定义格式如下：
<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:SSwith optional .mmmmilliseconds is required to work on all SQL databases.
中间有一些定义只是链接回这些定义，但简而言之YYYY-MM-DD HH:MM:SS，.mmm在所有 SQL 数据库上工作都需要可选的毫秒数。
