Differences between cast and convert in SQL Server

Both cast and convert in SQL Server convert an expression from one data type to another data type. Because SQL Server provides two features, it can be confusing to choose which feature should be chosen or in which case the feature should be used. Convert is designed for use with SQL Server, allowing for greater flexibility in converting between date and time values and decimals. Cast is a more ANSI-standard feature in two functions, that is, although more portable (for example, the use of cast functions can be more easily used by other database software), but the function is relatively weak. However, you still need to use cast when the decimal number is converted to a numeric value and the decimal value in the original expression is preserved. Therefore, I recommend that you first use cast, and then use convert if you encounter situations where you must use convert.

But not all conversion operations cast and convert are equivalent, let's look at the difference between cast and convert.

For a simple example, there is a type of float converted to numeric (16,2)

Select CAST (2014.0607 as numeric (16,2)) as Castvalue

Select CAST (2.0140607E+3 as numeric (16,2)) as Castvalue

Cast can be converted to the expected 2014.06.

Select CONVERT (numeric (16,2), 2014.0607) as Convertvalue

Can be converted to the expected 2014.06.

Select CONVERT (numeric (16,2), 2.0140607E+3) as Convertvalue

The result of running a version earlier than SQL Server 2012 is 0.00

SQL Server 2012 is expected to be 2014.06.

* This may be Microsoft has found and fixed this error

Conclusion

Convert just shows the conversion, DateTime to String, you can specify the format, this can be viewed in MSDN http://msdn.microsoft.com/zh-cn/library/ms187928.aspx
Cast is cast, the format cannot be specified when a datetime is converted to a string.