在SQL Server 2008中将字符串转换为日期时间

Simple question cannot seem to get right.

简单的问题似乎无法正确。

I have a table with a date in this format 20/09/2012 as varchar.

我有一个表格,其日期格式为20/09/2012,为varchar。

I need to convert to a datetime

我需要转换为日期时间

Tried various solutions but cannot get it right.

尝试了各种解决方案,但无法做到正确。

Any suggestions?

Thanks

1 个解决方案

#1

Using the CONVERT function with style 103 (British/European) seems to work for me:

使用风格103(英国/欧洲)的CONVERT功能似乎对我有用:

DECLARE @input VARCHAR(20) = '20/09/2012'

SELECT
    CONVERT(DATETIME, @input, 103)

With this approach, you could convert all your VARCHAR dates to their proper datatype - DATETIME - and store them as DATETIME in the first place ....

使用这种方法,您可以将所有VARCHAR日期转换为正确的数据类型 - DATETIME - 并将它们作为DATETIME存储在第一位....

See Aaron Bertrand great blog post on bad habits to kick : mis-handling date / range queries for more background info on why it's really really bad to keep storing dates as VARCHAR columns ....

请参阅Aaron Bertrand关于坏习惯的博客文章:错误处理日期/范围查询以获取更多背景信息,了解为什么将日期存储为VARCHAR列真的非常糟糕....

#1