![转换十进制时间长度超过 3 位的日期时间?](https://linux22.com/image/1480565/%E8%BD%AC%E6%8D%A2%E5%8D%81%E8%BF%9B%E5%88%B6%E6%97%B6%E9%97%B4%E9%95%BF%E5%BA%A6%E8%B6%85%E8%BF%87%203%20%E4%BD%8D%E7%9A%84%E6%97%A5%E6%9C%9F%E6%97%B6%E9%97%B4%EF%BC%9F.png)
我正在尝试将一些数据从 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 中。