0
DECLARE @JSON AS varchar(MAX)

SET @JSON = '[
 {
"UserId": "XYZ12345",
"LoginTime": "2021-12-25T07:48:59Z"
 },
 {
"UserId": "XYZ67890",
"LoginTime": "2021-12-24T07:48:59Z"
    }
  ]'

SELECT * 
FROM OPENJSON(@json) 
WITH ( 
         UserId nvarchar(MAX) '$.UserId', 
         LoginTime datetime '$.LoginTime'           
     ) 
ORDER BY UserId ASC

However, I get an error

Conversion failed when converting date and/or time from character string

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
PavanKumar GVVS
  • 859
  • 14
  • 45
  • 4
    [Cannot reproduce](https://dbfiddle.uk/?rdbms=sqlserver_2016&fiddle=3ffaaab290d186c5e1ced72876d13cdf). Language settings should not influence this either. You can try `DATETIMEOFFSET` instead, since it's explicitly designed to cope with the time zone (you can then convert this further if you have no need of time zone information), but if that "fixes" it there's still something weird going on. – Jeroen Mostert Dec 28 '21 at 09:44
  • 1
    Agreed, the format you have used is unambiguous, so that implies you actually have date/time values are are completely invalid. Such as `2021-02-29T25:00:17Z`. – Thom A Dec 28 '21 at 09:46
  • 1
    @Larnu this will produce a different error message (at least in 2019 - `The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.`) However I agree it's most likely caused by invalid data - but I suspect it's probably more like a typing error - someone used `O` instead of `0` or something like that. – Zohar Peled Dec 28 '21 at 09:51
  • 1
    Please update this [working DB Fiddle](https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=b9a367fe58cdd46a465618e8361dc0ee) to reproduce the error – Stu Dec 28 '21 at 10:15
  • Notably, this exact error will happen if there are time zones in the input that are *not* `Z`. Apparently the conversion to `DATETIME` is only silently allowed if a UTC time zone string is used; for other time zone offsets `DATETIMEOFFSET` must be used. Using `DATETIMEOFFSET` explicitly if the input is known to contain time zone indicators is a good idea anyway, of course. – Jeroen Mostert Dec 28 '21 at 10:22

1 Answers1

0

Some of the logintime's in the JSON data probably have timezones.

This raises an error when converting it to DATETIME.

But it's fine when those are converted to DATETIME2 or DATETIMEOFFSET.

And DATETIMEOFFSET is then probably the better option, since it'll contain the timezone.

Simplified tests:

SELECT * 
FROM OPENJSON('[{"LoginTime": "2021-12-25T07:48:59+01:00"}]') 
WITH ( LoginTime datetime '$.LoginTime' ) 
GO

Msg 241 Level 16 State 1 Line 1
Conversion failed when converting date and/or time from character string.

SELECT * 
FROM OPENJSON('[{"LoginTime": "2021-12-25T07:48:59+01:00"}]') 
WITH ( LoginTime datetime2 '$.LoginTime' ) 
GO

| LoginTime                   |
| :-------------------------- |
| 2021-12-25 07:48:59.0000000 |

SELECT * 
, SWITCHOFFSET(LoginTime, '+00:00') AS LoginTimeZulu
FROM OPENJSON('[{"LoginTime": "2021-12-25T07:48:59+03:00"}]') 
WITH ( LoginTime datetimeoffset '$.LoginTime' ) 
GO

LoginTime                          | LoginTimeZulu                     
:--------------------------------- | :---------------------------------
2021-12-25 07:48:59.0000000 +03:00 | 2021-12-25 04:48:59.0000000 +00:00

db<>fiddle here

LukStorms
  • 28,916
  • 5
  • 31
  • 45
  • Ouch. It's a bit of a misfeature that it allows silent conversion to `DATETIME2` while *discarding* the TZ info, since that has no hope of being correct unless all the offsets happen to be the same. This makes it all the more important to use `DATETIMEOFFSET` if time zones are involved (which can then later be converted, if desired). – Jeroen Mostert Dec 28 '21 at 12:44
  • @JeroenMostert It's even worse. A CAST of the DATETIMEOFFSET to a DATETIME2 or a DATETIME doesn't change the time according the timezone. – LukStorms Dec 28 '21 at 13:10