What is the difference between cast and convert in SQL?

If SQL ServerProgramA member wants to replace the expression with another one. He can choose from the two built-in functions of SQL Server 7 and 2000. In stored procedures or other situations, we often need to convert data from datetime type to varchar type; convert and cast can be used in this case.

Because SQL Server provides two functions, it is easy to confuse which function should be selected or under which situation this function should be used. Convert is designed for SQL Server, making conversion between date, time, and decimal places more flexible.

Cast is a more ANSI standard feature of the two features. Although cast functions are more portable (for example, cast functions can be more easily used by other database software), cast functions are relatively weak. However, cast is still required when the decimal number is converted to a value and the decimal number in the original expression is retained. Therefore, we recommend that you use cast first. If convert is required, use convert.

Cast and convert can also be used together to achieve special effects. For example, the following method is generally used to generate a char variable under current date:

Select convert (char (10), current_timestamp, 102)

(102 indicates that the ANSI date mode is used, that is, the yy. Mm. dd type)

However, if you want to explicitly generate this variable as a datetime or smalldatetime variable to be compatible in a specific database column, you can use the following statement:

Select cast (convert (char (10), current_timestamp, 102) as datetime

The returned value will be yy. Mm. dd 00:00:00 (for example, am as the timestamp;