在SQL Server中将varchar转换为datetime2

I'm using SQL Server 2008 R2 and I did an import from a flat file. I couldn't import the datetime column properly so I specified it temporarily as a varchar(50). Now I want to convert it to the datetime2 format. However when doing so I get the error -

我正在使用SQL Server 2008 R2,我从平面文件导入。我无法正确导入datetime列,因此我暂时将其指定为varchar(50)。现在我想将其转换为datetime2格式。但是,这样做我得到错误 -

Conversion failed when converting date and/or time from character string.

从字符串转换日期和/或时间时转换失败。

The data that is currently in my varchar(50) column looks like this:

当前在我的varchar(50)列中的数据如下所示:

2008-04-02-16.43.32.179530
2009-01-12-20.15.41.936632
2009-02-18-16.54.49.071662

What's the best way to convert it to datetime2?

将它转换为datetime2的最佳方法是什么?

2 个解决方案

#1

Not pretty

;WITH T(YourCol) As
(
SELECT '2008-04-02-16.43.32.179530' union all
SELECT '2009-01-12-20.15.41.936632' union all
SELECT '2009-02-18-16.54.49.071662'
)
SELECT CAST(
            STUFF(REPLACE(
                          STUFF(YourCol,11,1,'T')
                  ,'.',':')
            ,20,1,'.')
        AS DATETIME2)
FROM T

#2

Unfortunately, that's not a recognized timestamp format. It's close to ODBC Canonical format, but not quite. You'll have to do some string manipulation.

不幸的是,这不是公认的时间戳格式。它接近ODBC Canonical格式,但并不完全。你必须做一些字符串操作。

If you take the first 10 characters, that's the date in ODBC Canonical (yyyy-mm-dd) format, no changes needed. That's easy enough: LEFT(dateAsStringColumn, 10). Now, you need characters 12 through 19 (skipping the dash between date and time), but with the "." replaced by ":". Lastly, tack on characters 20-26 verbatim. Convert that whole thing to a Datetime2 using style 21 (ODBC Canonical with milliseconds).

如果你取前10个字符,这是ODBC Canonical(yyyy-mm-dd)格式的日期,则不需要进行任何更改。这很容易:LEFT(dateAsStringColumn,10)。现在,您需要字符12到19(跳过日期和时间之间的短划线),但是使用“。”取而代之 ”:”。最后,逐字地对20-26字符进行处理。使用样式21(ODBC Canonical with milliseconds)将整个事物转换为Datetime2。

Try this:

SELECT CONVERT(datetime2, LEFT(myColumn, 10) + " " + REPLACE(SUBSTRING(myColumn, 12, 8), ".", ":") + RIGHT(myColumn, 7), 21) as DateFromString FROM myTable

#1