SQL DATETIMEOFFSET (Eventdatetime ) - Conversion failed when converting date and/or time from character string

Question

When I try to convert the value '2021-03-01T00:08:41.000Z' into a DATETIMEOFFSET it is successful. But when I try to convert the value '2021-03-01T0:08:41.000Z' into a DATETIMEOFFSET its failing.

After analysis, with the following script one is not valid data. But I need to process with both the format.

Please can someone suggest how I can store'2021-03-01T0:08:41.000Z' also as a DATETIMEOFFSET value?

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

SQL Script- error with T0 , no error with 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

You'll need to do some string manipulation to detect this case and correct for it. Find the 'T', find the next ':' and if there is only one character between them, insert a '0'.

The following is based on the assumption that this is the only data anomaly you are dealing with. It may need to be made more robust if the data can be incorrect in other ways.

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);

Ideally of course you would ask your downstream source to correct their formatting and provide a valid datetimeoffset format.