☰

转换十进制时间长度超过 3 位的日期时间？

tag-icon • tag-icon sql query ms-sql
转换十进制时间长度超过 3 位的日期时间？

我正在尝试将一些数据从 CSV 导入 MS SQL 2008 Server。我正在使用 PowerShell 的 Invoke-SQL 和利用的 SQL 脚本BULK INSERT。

CSV 字段作为 VarChars 导入，我正在尝试指定数据类型。

在 CSV 中提供的数据（我无法控制）中，一些日期时间字段具有以下格式的日期/时间：

2012-03-15 15:10:08.920000000

通常我只会使用 ALTER，然后让 SQL 进行转换，通常这样做就可以了...例如：

ALTER TABLE [dbo].[ImportData] ALTER COLUMN [PlanSetupDate] datetime null;

但是，当它遇到上述某个 DateTime 时，它​​会失败并显示错误消息：

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

接下来我尝试了 SQL 的 Convert：

Select Convert(datetime, '2012-03-15 15:10:08.920000000')

但我收到与使用 ALTER 时相同的错误消息。

如果我删去多余的小数位（留下三位或更少），转换就会按预期进行，例如：

2012-03-15 15:10:08.920

我不能只是转换之前截断字段的最后 X 个字符，因为列中的大多数其他日期时间都是更传统的格式，例如2010-01-05 00:00:00。

虽然我想保留它，但时间部分实际上并不那么重要，绝对不是小数点后的任何内容。因此，如果转换它的解决方案需要在小数点处截断它，那也没关系。:)

我总是可以在通过 PowerShell 或类似方法将 CSV 导入 SQL 之前对其进行修改，但如果可能的话，我更愿意在 SQL 中完成所有数据处理。

那么，有没有办法通过 SQL 查询来转换这种有问题的日期时间格式？

答案1

DATETIME2是您要查找的 SQL 数据类型。它需要精确到小数点后 7 位的秒数。TechNet 提供了完整的信息，并提供了比较各种类型的示例这里。

下面将为您提供有关所有适用类型的基础知识：

SELECT 
     CAST('2007-05-08 12:35:29. 1234567 +12:15' AS time(7)) AS 'time' 
    ,CAST('2007-05-08 12:35:29. 1234567 +12:15' AS date) AS 'date' 
    ,CAST('2007-05-08 12:35:29.123' AS smalldatetime) AS 'smalldatetime' 
    ,CAST('2007-05-08 12:35:29.123' AS datetime) AS 'datetime' 
    ,CAST('2007-05-08 12:35:29. 1234567 +12:15' AS datetime2(7)) AS 'datetime2'
    ,CAST('2007-05-08 12:35:29.1234567 +12:15' AS datetimeoffset(7)) AS 'datetimeoffset';

产生结果：

Data type       Output
time            12:35:29. 1234567
date            2007-05-08
smalldatetime   2007-05-08 12:35:00
datetime        2007-05-08 12:35:29.123
datetime2       2007-05-08 12:35:29.1234567
datetimeoffset  2007-05-08 12:35:29.1234567 +12:15

以上查询和结果直接取自上面引用的 TechNet 页面。

NB我相信首次DATETIME2出现在 SQL 2008 中。

相关内容