格式化Blob和SQL数据库之间的日期和时间 [英] Formatting date and time between blobs and SQL database

问题描述

你好！

我有一个将文件转储到Azure blob中的过程.其中，斑点包含格式为"2018-09-05T06:46:28.2485598Z"的时间信息.我想将数据从Blob读取到Azure SQL数据库中，并具有临时的 info基本上以任何SQL支持的datetime格式维护.我认为，这应该是一项例行任务.但是，我在转换时遇到了一些麻烦:

I have a process which dumps files into Azure blobs. Among other things, the blobs contain temporal information in the format "2018-09-05T06:46:28.2485598Z". I'd like to read the data from blobs into a Azure SQL database and have the temporal info maintained basically in any SQL supported datetime format. This should be a routine task, I assume. However, I'm having some trouble with the transformation:

ErrorCode = UserErrorInvalidDataValue，'Type = Microsoft.DataTransfer.Common.Shared.HybridDeliveryException，Message =列'XXX'包含无效值'2018-09-05T06:56:49.0536346Z'. 无法将格式为'yyyy-MM-dd HH:mm:ss.fffffffz'的'2018-09-05T06:56:49.0536346Z'转换为类型为'DateTime'.，Source = Microsoft.DataTransfer.Common，''Type = System .FormatException，Message = String无法识别为有效的DateTime.，Source = mscorlib，'

ErrorCode=UserErrorInvalidDataValue,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Column 'XXX' contains an invalid value '2018-09-05T06:56:49.0536346Z'. Cannot convert '2018-09-05T06:56:49.0536346Z' to type 'DateTime' with format 'yyyy-MM-dd HH:mm:ss.fffffffz'.,Source=Microsoft.DataTransfer.Common,''Type=System.FormatException,Message=String was not recognized as a valid DateTime.,Source=mscorlib,'

有没有建议如何指定正确的格式/文化组合-否则还有什么不对吗?我已经尝试了很多-显然我在这里遗漏了一些东西.谢谢.

推荐答案

对于Datetime类型，Azure SQL数据库 小数点后两位数字.所以我认为问题出在Azure SQL中类型为Datetime的列不支持值 例如:

For Datetime type, Azure SQL database supports up to three fractional second digits. So I think the problem lies on the column with type Datetime in Azure SQL doesn't support value like: '2018-09-05T06:56:49.0536346Z', which apparently exceeds this limit.

分数秒可以选择最多使用七位数字 Fracative 可以选择最多使用七位数字来指定秒(

You could modify the type definition of the database column as Datetime2 (Fractional seconds can optionally be specified using up to seven digits) or DatetimeOFFSET (Fractional seconds can optionally be specified using up to seven digits) as a workaround.

谢谢.

This doc will give you more details. Thanks.

这篇关于格式化Blob和SQL数据库之间的日期和时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！