Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
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.