CAST and CONVERT区别

The CAST and CONVERT functions are very similar: Both translate a value from one data type to another. Although their performance is also similar, their syntax and potential usage is slightly different.

Both CAST and CONVERT have a straightforward syntax:

CAST(expression AS new_data_type)

CONVERT(new_data_type, expression, [style])

The expression must already have a data type that is translatable into the new_data_type. For instance, you can't convert an alphanumeric string into an integer.

NOTE

CONVERT has an optional parameter: style. This parameter is allowed only for cases when working with date and time values. SQL Server supports numerous formats for presenting date and time values; the style parameter is used to specify such format.

For example, suppose that we want to retrieve order dates from the sales table. However, we don't care about the time portion; all we need to know is the order date. We could use either CAST or CONVERT to do this, as in the following queries:

SELECT TOP 1 CAST(ord_date AS VARCHAR(12)) FROM sales

or

SELECT TOP 1 CONVERT(VARCHAR(12), ord_date, 109) FROM sales

Both return the same results:

------------ 
Sep 14 1994

In this example, we retrieved a date value in its default format (on my server). In fact, CONVERT(VARCHAR(12), ord_date) would bring the same result. Now, suppose that we need a date value in which month, day, and year are separated by dashes. In such a case, we have no choice but to resort to CONVERT with the style 110:

SELECT TOP 1 CONVERT(VARCHAR, ord_date, 110) FROM sales

Results:

------------ 
09-14-1994

For a complete listing of supported styles for presenting date and time values, refer to SQL Server online documentation.

 

转自：

http://www.informit.com/articles/article.aspx?p=31283&seqNum=4