SQL Server Brain Basher of the Week #032 - TSQL - Converting from one value to another

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.

1. CAST
   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.
2. CONVERT
   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.
3. PARSE
   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.
4. 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') ;