What is CONVERT and TRY_CONVERT

CAST is the ANSI standard SQL function for converting between data types, and is used in many database systems. In Transact-SQL, you can also use the CONVERT function, as shown here:

SQLCopy

SELECT CONVERT(varchar(4), ProductID) + ': ' + Name AS ProductName
FROM Production.Product;

Once again, this query returns the value converted to the specified data type, like this:

ProductName

680: HL Road Frame – Black, 58

706: HL Road Frame – Red, 58

707: Sport-100 Helmet, Red

708: Sport-100 Helmet, Black

…

Like CAST, CONVERT has a TRY_CONVERT variant that returns NULL for incompatible values.

Another benefit of using CONVERT over CAST is that CONVERT also includes a parameter that enables you specify a format style when converting numeric and date values to strings. For example, consider the following query:

SQLCopy

SELECT SellStartDate,
       CONVERT(varchar(20), SellStartDate) AS StartDate,
       CONVERT(varchar(10), SellStartDate, 101) AS FormattedStartDate 
FROM SalesLT.Product;

The results from this query might look something like this:

SellStartDate

StartDate

FormattedStartDate

2002-06-01T00:00:00.0000000

Jun 1 2002 12:00AM

6/1/2002

2002-06-01T00:00:00.0000000

Jun 1 2002 12:00AM

6/1/2002

2005-07-01T00:00:00.0000000

Jul 1 2005 12:00AM

7/1/2005

2005-07-01T00:00:00.0000000

Jul 1 2005 12:00AM

7/1/2005

…

…

…

 Note

To find out more about style formatting codes you can use with CONVERT, see the Transact-SQL reference documentation.

PARSE and TRY_PARSE

The PARSE function is designed to convert formatted strings that represent numeric or date/time values. For example, consider the following query (which uses literal values rather than values from columns in a table):

SQLCopy

SELECT PARSE('01/01/2021' AS date) AS DateValue,
   PARSE('$199.99' AS money) AS MoneyValue;

The results of this query look like this:

DateValue

MoneyValue

2021-01-01T00:00:00.0000000

199.99

Similarly to CAST and CONVERT, PARSE has a TRY_PARSE variant that returns incompatible values as NULL.

 Note

When working with decimal or numeric data types, you may need to round to a whole number or set the decimal point, which can be achieved through precision and scale. To better understand this concept of precision and scale, see the Transact-SQL reference documentation.

STR

The STR function converts a numeric value to a varchar.

For example:

SQLCopy

SELECT ProductID,  '$' + STR(ListPrice) AS Price
FROM Production.Product;

The results would look something like this:

ProductID

Price

680

$1432.00

706

$1432.00

707

$35.00

…

…