Differences between cast and convert in SQL Server

Source: Internet
Author: User

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.