Presto SQL - 将日期字符串转换为日期格式

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文档并尝试了各种各样的东西(cast,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?

我怎么转换这个?

5 个解决方案

#1

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

#2

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:SS with optional .mmm milliseconds is required to work on all SQL databases.

之间有一些定义只是链接回这些,但简而言之,YYYY-MM-DD HH:MM:SS,可选.mmm毫秒,需要在所有SQL数据库上工作。

#3

Blockquote

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;

从Table_Name limit 10中选择CAST(date_format(date_parse(cast(dateid as varchar(10)),'%Y%m%d'),'%Y /%m-%d')AS DATE);

#4

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;

从Table_Name limit 10中选择CAST(date_format(date_parse(cast(dateid as varchar(10)),'%Y%m%d'),'%Y /%m-%d')AS DATE);

#5

try this :

尝试这个 :

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

output :

2015-07-14 08:22:39.000