SQL Server datetimeoffset and for json auto changing datetimeoffset to datetime

Question

I am selecting a record from a table that has multiple datetimeoffet columns, when I add the for json auto it converts it from

2017-06-21 08:12:16.1430000 +00:00

to

2017-06-21T08:12:16.1430000Z

This is my code:

select LastUpdateTimestamp 
from table
where id = 1
for json auto

which produces this output:

[{"LastUpdateTimestamp":"2017-06-21T08:12:16.1430000Z"}]

How can I keep it in the format without it being converted, or how can I convert it in C# back to that format? I am doing a lot of dynamic work and I can't rely on all dates to fit a certain format.

I cannot reproduce this. What SQL Server version and service pack are you using? — Dan Guzman, May 31 '18 at 19:12
column is datatimeoffset(7) put 2017-06-21T08:12:16.1430000Z as the value in the column then run select LastUpdateTimestamp from table for json auto and it converts it to a json datatime. That is the problem — user1552172, May 31 '18 at 19:18

score 0 · Answer 1 · answered May 31 '18 at 20:07

There is no JSON datetime data type nor standard for string formats for datetime data in JSON. The underlying SQL Server datetimeoffset(7) data type is a 10-byte binary structure with no notion of format so it cannot be represented in a JSON string without conversion.

SQL Server returns ISO 8601 formatted strings by default with JSON AUTO. The problem you are experiencing is that the '+00:00' is suppressed with UTC; the offset is included only when the UTC offset is non-zero.

You can use the FORMAT T-SQL function to control the string format if the JSON AUTO defaults are not sufficient:

FORMAT(LastUpdateTimestamp,'O') AS LastUpdateTimestamp

The part that makes using the format difficult or i would have done it already is i am grabbing * on the result as it is running a bit of code dynamically over multiple tables. I can build out some code to break the * up into actual column names and if its datatimeoffset run the format — user1552172, Jun 05 '18 at 16:47

score -1 · Answer 2 · answered May 31 '18 at 17:16

-1

select DATE_FORMAT
      (LastUpdateTimestamp,'%d %M %Y')
        from table where id = 1

Use MySQL DATE_FORMAT function

answered May 31 '18 at 17:16

primo

1,340
3
12
40

I just want the raw value to come over not converted in the for json. – user1552172 May 31 '18 at 17:38
or a way to convert it back without specifying a format – user1552172 May 31 '18 at 17:47
1

Why suggest a MySQL function as an answer to a Microsoft SQL Server Question? – Dan Guzman May 31 '18 at 19:01

SQL Server datetimeoffset and for json auto changing datetimeoffset to datetime

2 Answers2