Solved: SQL Server and problem with timezone for json results (FOR JSON PATH)

 

If You use on SQL Server select statements with output result to json structure, for instance

select * from Table FOR JSON PATH

You may feel surprise in case of DATE type and for many developers  makes headache. The problem is losing time zone after converting data to json. For example: in the database we have columns date_from with timezone value 2017-04-05T15: 00: 00.000Z after using FOR JSON PATH we will get 2017-04-05T15: 00:00. The problem is serious when our systems record dates using different time zones. Problem is on Microsoft side, but You can avoid it.

If You have any columns with DATE Type then You should use format function CONVERT(datetimeoffset, ColumnName) as ColumnName like below presented:

select CONVERT(datetimeoffset, DateTimeNote) as DateTimeNote  from Table FOR JSON PATH

Thanks to datetimeoffset parameter  timezone in data of column will be not lost

May 4th, 2017

  • There are some interesting cut-off dates on this article however I don抰 know if I see all of them heart to heart. There is some validity however I’ll take hold opinion till I look into it further. Good article , thanks and we wish more! Added to FeedBurner as effectively.