SQL DATETIMEOFFSET (Eventdatetime )-转换从字符串中转换日期和/或时间时失败

Question

当我尝试将值'2021-03-01T00:08:41.000Z‘转换为DATETIMEOFFSET时，它是成功的。但是，当我试图将值'2021-03-01T0:08:41.000Z‘转换为DATETIMEOFFSET时，它失败了。

经过分析，使用下面的脚本一个是无效的数据。但我需要处理这两种格式。

请有人建议我如何也可以store'2021-03-01T:08:41.000Z‘作为一个DATETIMEOFFSET值？

select ISDATE('2021-03-01T00:08:41.000Z')  --> 1 
select ISDATE('2021-03-01T0:08:41.000Z')   --> 0 

SQL脚本- T错误，T00无错误

declare @jsonArray varchar(max) ='[{   "number": "AC100",  "eventdatetime": "2021-04-09T0:00:00+00:00",  "val": "0"}]';

WITH jsonData AS (
    SELECT  number, eventdatetime,val
    FROM OPENJSON(@jsonArray)
    WITH (
        number VARCHAR(50),
        eventdatetime DATETIMEOFFSET ,
        val VARCHAR(50)
    )
)
SELECT number, eventdatetime, val FROM jsonData WHERE val IS NOT NULL; 

Answer 1

您需要执行一些字符串操作来检测这种情况并对其进行更正。找到'T'，找到下一个：‘如果它们之间只有一个字符，插入'0’。

以下是基于这样一个假设，即这是您正在处理的唯一数据异常。如果数据在其他方面可能是不正确的，则可能需要使其更加健壮。

select x.TestDate
  , cast(case when SecondPos-FirstPos = 2 then substring(x.TestDate,1,FirstPos) + '0' + substring(x.TestDate,FirstPos+1,len(x.TestDate)) else x.TestDate end as datetimeoffset(0)) Corrected
from (
  values
  ('2021-03-01T00:08:41.000Z')
  ,('2021-03-01T0:08:41.000Z')) x (TestDate)
cross apply (
  values
  (charindex('T',TestDate), charindex(':',TestDate))
) y (FirstPos, SecondPos);

当然，理想情况下，您可以要求下游源更正它们的格式，并提供有效的datetimeoffset格式。