With T-SQL queries, converting data between data types is very common. Conversion can happen automatically (implicitly) or we can take the control and convert as we want (explicitly). This week Brain Basher is based on it, here it is;
What are the functions available with SQL Server 2014 for converting data between data types?
SQL Server offers multiple functions for converting data between types. Some are based on ANSI-Standard and some are SQL Server specific with additional functionalities. Here are four functions available for converting data.
CAST is an ANSI-Standard function, hence recommended over SQL Server specific functions. It is a scalar function and returns an error if data types are incompatible.
CONVERT is proprietary to SQL Server and provides additional functionalities over CAST. This function accepts optional style number for formatting returned value. This is also a scalar function.
This is special function that converts a string to date, time, and number type. The optional parameter which accepts the culture converts the value based on it. If it is not submitted, culture setting of the current user will be used.
- TRY_PARSE and TRY_CONVERT
These two functions work just like PARSE and CONVERT. However, instead of raising run-time error for incompatible types, failed conversion return NULL.
Here are some sample codes;
USE AdventureWorks2014; GO -- CAST function, converting datetime to date SELECT SalesOrderId, CAST(OrderDate AS date) OrderDate FROM Sales.SalesOrderHeader; -- CONVERT function SELECT SalesOrderId, CONVERT(char(10), OrderDate, 101) OrderDate FROM Sales.SalesOrderHeader; -- PARSE function SELECT PARSE('01/01/2015' AS datetime2 USING 'en-US') ; -- TRY_PARSE and TRY_CONVERT function SELECT PARSE('13/01/2015' AS datetime2 USING 'en-US') ; -- this throws an error SELECT TRY_PARSE('13/01/2015' AS datetime2 USING 'en-US') ;