In SQL Server, the FOR XML clause allows us to return the results of a query as an XML document. Simply by placing the FOR XML clause at the end of the query will output the results in XML.
When we do this, we have the option of specifying RAW, AUTO, EXPLICIT, or PATH mode. These modes allow us to shape the resulting XML in different ways, and so the mode we choose will determine how the XML is generated.
Below are examples of using AUTO mode when generating XML from a SQL query.
In SQL Server, the FOR XML clause allows us to return the results of a query as an XML document. Simply by placing the FOR XML clause at the end of the query will output the results in XML.
When we do this, we have the option of specifying RAW, AUTO, EXPLICIT, or PATH mode. These modes allow us to shape the resulting XML in different ways, and so the mode we choose will determine how the XML is generated.
Below are examples of using PATH mode when generating XML from a SQL query.
Below is a list of binary styles supported by the CONVERT() function in SQL Server. These are the styles that can be used as the third argument to indicate how the input value is formatted when converting to binary.
We can use these styles when the input expression is binary(n), char(n), varbinary(n), or varchar(n).
Continue readingBelow is a list of XML styles supported by the CONVERT() function in SQL Server. These are the styles that can be used as the third argument to indicate how the input value is formatted when converting to XML.
Below is a list of date & time styles supported by the CONVERT() function in SQL Server. These are the styles that can be used as the third argument to indicate how the input value is formatted.
Below is a list of numeric styles supported by the CONVERT() function in SQL Server. These are the styles that can be used as the third argument to indicate how the input value is formatted.
If you’re getting an error that reads Argument data type datetime2 is invalid for argument 1 of isdate function, it’s because you’re passing a datetime2 value to the ISDATE() function, but this function doesn’t work with datetime2 values.
To fix this issue, either pass a valid date type or use the work around below to provide similar functionality that works with datetime2 values.
Continue readingIn SQL Server, the TRY_PARSE() function returns the result of an expression, translated to the requested data type, or NULL if the conversion fails.
Basically, it works the same as the PARSE() function, except that it returns NULL instead of an error if the cast fails.
Both functions are intended for converting string values to either date/time or number types.
Continue readingIn SQL Server, the PARSE() function returns the result of an expression, translated to the requested data type.
Basically, it enables us to parse a string expression to the specified data type. It’s intended for converting string values to either date/time or number types.
The PARSE() function can be handy when attempting to convert with CAST() or CONVERT() fails. The PARSE() function is able to parse the expression, and this may result in certain values being converted that wouldn’t normally be able to be converted.
When it comes to converting date and time values in SQL Server, things can get a little complex. But in this article I hope to demystify this topic for anyone who’s confused when it comes to making conversions between dates and other data types, as well as changing the format of date/time values, and other considerations.
Let’s jump straight in.
Continue reading