Use the SQL CONVERT function to change an input expression from one data type into another specified data type. For example, a decimal value to an integer or a date value to a string.

Description

The SQL CONVERT function is one of the SQL functions to change the data type of a variable or an expression. Just like the CAST function, the CONVERT function helps in explicitly changing the data types. It helps to meet the programming and data manipulation requirements in SQL.

SQL CONVERT Usage Notes

The data type as the first argument of the CONVERT function is the output data type. For varchar, nchar, char, nvarchar, binary, varbinary, there is an optional length parameter. It indicates the length of the result with the new data type. The default length value is 30.

The expression as the second argument of the CONVERT function is the original value to convert into another data type as indicated by the data type argument.

Style as the final argument is also optional. It describes how the conversion will take place between the data types. It is an integer value indicating the format of the return data type. For example, different integer values indicate different styles for float, real, money, datetime, etc. expressions.

Syntax

CONVERT (datatype (length) , expression, style)

SQL CONVERT Examples

Below is a simple SQL query using the CONVERT function to change decimal data type to a string data type.

SELECT CONVERT(varchar, 56.99) ConvertedNumber;
/* Answer */
SELECT CONVERT(varchar, 56.99) ConvertedNumber;

The following example uses the AdventureWorks2019 database. The query returns due date column values for a purchase order in a string format as defined by the style argument.

SELECT CONVERT(nvarchar, DueDate, 6) FormattedDate FROM Purchasing.PurchaseOrderDetail
/* Answer */
SELECT CONVERT(nvarchar, DueDate, 6) FormattedDate
FROM Purchasing.PurchaseOrderDetail

The CONVERT function converts a due date into a string value from a datetime data type. Different styles values return due dates in various formats.

SELECT CONVERT(int, UnitPrice) PriceAsInteger FROM Purchasing.PurchaseOrderDetail
/* Answer */
SELECT CONVERT(int, UnitPrice) PriceAsInteger
FROM Purchasing.PurchaseOrderDetail

The above query selects the unit price column of purchasing order details table. The CONVERT function converts the money data type of unit price values to integer data type.

See Also

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Are YOU READY TO START LEARNING SQL SERVER, BUT DON'T KNOW WHERE TO START?

Nothing is worse than, being excited to learn a new tool but not knowing where to start, wasting time learning the wrong features, and being overwhelmed .

But it doesn't have to be this way.

I'm Putting together a free email course to help you get started learning SQL Server.

Name*
Email*
>