As mentioned earlier, JSON does not have the same data types as SQL Server. Therefore, when JSON text is generated from relational data, a data type conversion is performed. The FOR JSON clause uses the following mapping to convert SQL Server data types to JSON types in the JSON output:
Conversion between SQL Server and JSON data types
|
SQL Server data type |
JSON data type |
|
Char, Varchar, Nchar, NVarchar, Text, Ntext, Date, DateTime, DateTime2, DateTimeOffset, Time, UniqueIdentifier, Smallmoney, Money, XML, HierarchyId, Sql_Variant |
string |
|
Tinyint, Smallint, Int, Bigint, Decimal, Float, Numeric |
number |
|
Bit |
true or false |
|
Binary, Varbinary, Image, Rowversion, Timestamp |
encoded string (BASE 64) ... |
