3

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user1552172
  • 614
  • 1
  • 9
  • 27
  • 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

2 Answers2

0

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
Dan Guzman
  • 43,250
  • 3
  • 46
  • 71
  • 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
-1
select DATE_FORMAT
      (LastUpdateTimestamp,'%d %M %Y')
        from table where id = 1

Use MySQL DATE_FORMAT function

primo
  • 1,340
  • 3
  • 12
  • 40