Two of the many T-SQL functions available in SQL Server are JSON_QUERY() and JSON_VALUE(). These functions can be used to extract data from JSON documents.
Their general syntax is similar, and at first glance, you might think they do exactly the same thing, but they don’t. There’s definitely a place for both functions when working with JSON and SQL Server.
This article looks at the difference between JSON_QUERY() and JSON_VALUE().
When using JSON with SQL Server, you can use the JSON_QUERY() function to extract an object or an array from a JSON string.
To use this function, you provide the JSON expression as an argument. You can also provide a second (optional) argument to specify the object or array to extract.
When using JSON with SQL Server, you can use the JSON_VALUE() function to return a scalar value from a JSON string.
To use this function, you provide two arguments; the JSON expression, and the property to extract.
When using SQL Server, you can use the ISJSON() function to test whether or not a string expression contains valid JSON.
If the expression contains valid JSON, ISJSON() returns 1, otherwise it returns 0.
From SQL Server 2022, we also have the option of testing for a specific JSON type.
Continue readingWhen using SQL Server, you can use the FOR JSON clause in a query to format the results as JSON. When doing this, you must choose either the AUTO or the PATH option. This article contains examples of using the PATH option.
In SQL Server you can use the FOR JSON clause in a query to format the results as JSON. When doing this, you must choose either the AUTO or the PATH option. This article contains examples of using the AUTO option.
This post contains a list of Transact-SQL date and time functions available in SQL Server (as of SQL Server 2017).
This includes functions that return the date or parts of the date, as well as functions that manipulate and/or format the date.
In SQL Server, the first day of the week is determined by the current language settings. You can also override that with the SET DATEFIRST statement, which allows you to explicitly set the first day of the week.
In either case, you can use the @@DATEFIRST function to find out what settings your session is using for the first day of the week. This article demonstrates how.
In SQL Server, you can use SET DATEFIRST to set the first day of the week.
The first day of the week can be different, depending on the language being used. For example the default for us_English is 7 (Sunday), whereas the default for Deutsch (German) is 1 (Monday).
This article demonstrates how to change the first day of the week without changing the language.
In SQL Server, you can use the YEAR() function to return the “year” part of a date. This is returned as an integer.
Below are examples of how to use this function.