When working with databases, data conversion is an important aspect. It is an efficient way to manage and organize data into different data types so that they can be synchronized on multiple platforms. This will allow users to access, view, or modify data when required.
In the article, you will learn how to use the CONVERT() function in SQL Server with syntax and examples, as well as discuss the alternatives that exist to that function.
Contents
- What is a CONVERT function in SQL Server?
- SQL CONVERT function: Syntax
- SQL CONVERT function example
- Use the CONVERT function in the WHERE clause
- Alternatives to the CONVERT function
What is a CONVERT function in SQL Server?
SQL CONVERT explicitly converts an expression of one data type to another with formatting. The function returns the converted value, if the conversion succeeds, or returns an error if it fails.
Depending on the SQL Server version you use, the CONVERT function works differently. In earlier SQL Server versions such as 2005, 2008, and 2012, the CONVERT function returned a data type specified in the expression and returned NULL when a given data type argument was null. However, in late SQL Server versions such as 2016, the function returns NULL if the third parameter is null.
To move on, we’ll see how to use the SQL CONVERT function and discuss syntax, examples, and alternatives to the function.
SQL CONVERT function: Syntax
Now, let’s start with the syntax of the CONVERT function and have a detailed look at each argument.
The syntax of the function is as follows:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )The table describes the arguments used in the CONVERT function.
| Argument | Description |
| data_type | A data type you want to get in the result. |
| length (optional parameter) | An integer that specifies the length of the destination data type. |
| expression | A valid value to be converted. |
| style | An integer expression that instructs how the function will convert the expression. The specified data type defines the range of values for the style argument. For more information about style values, see the Microsoft documentation. |
Implicit and explicit data type conversion
Data types can be converted either implicitly or explicitly.
Implicit conversion occurs when data is converted from one data type to another automatically. In this case, you do not need to explicitly specify the CONVERT keyword in the query. Let’s compare the results of these two SQL queries.
-- the first query
SELECT
p.ProductId
,p.ProductName
,p.Price
FROM Production.Product p
WHERE p.Price > 1000
ORDER BY p.Price ASC;
-- the second query
SELECT
p.ProductId
,p.ProductName
,p.Price
FROM Production.Product p
WHERE p.Price > '1000'
ORDER BY p.Price ASC;
As you can see, both queries have the same output. It means that SQL Server has automatically converted ‘1000’ as text from the second query into the integer data type.
Explicit conversion takes place when data is converted from one data type to another manually. It means that you need to explicitly specify the CONVERT keyword in your query.
Let’s check this in the following SQL queries.
-- the first query
SELECT
p.ProductId
,p.ProductName
,p.Price
FROM Production.Product p
ORDER BY p.Price ASC;
-- the second query with the explicitly specified CONVERT function
SELECT
p.ProductId
,p.ProductName
,CONVERT (INT, p.Price) as NewPrice
FROM Production.Product p
ORDER BY p.Price ASC;
The result of the queries differ. In the second query, the function has converted the decimal data type of the Price column into an integer data type as specified in the query.
In the Microsoft documentation, you can find the table that illustrates data type conversions to be done implicitly or explicitly.
Note: The table has been taken from the official Microsoft documentation.
SQL CONVERT function example
The next step is to describe how the SQL CONVERT function works. For demo purposes, we are going to convert the following data types:
- Date
- Numeric
- Money
Example #1: Convert date and time data types
This example illustrates how to convert the current database system date and time into several formats. The GETDATE() function will be used to retrieve the current data and time. So, execute the following SELECT statement:
SELECT GETDATE() as "current_date",
CONVERT(VARCHAR, GETDATE(), 0) as current_date_0,
CONVERT(VARCHAR, GETDATE(), 104) as current_date_104,
CONVERT(VARCHAR, GETDATE(), 110) as current_date_110,
CONVERT(VARCHAR, GETDATE(), 113) as current_date_113,
CONVERT(VARCHAR, GETDATE(), 120) as current_date_120;Note: You can view the full list of date and time styles in the Microsoft documentation.
In the output, the SELECT statement returned the date in different formats according to the specified styles.
Depending on the data type you use, the resulting data can be either truncated or rounded. Let’s demonstrate this in the examples with numeric and money values.
Example #2: Convert numeric data types
When the precision in the numeric value is too big, the output data can be rounded. For example, execute the following query:
SELECT CONVERT(NUMERIC(10,3),'123.456789');
In the result, the value has been rounded to ‘123.457’. You can check a full list of data types, which can be truncated or rounded in the Microsoft documentation.
Example #3: Convert money data types
Suppose that the input data is $123.456789777. You want to display only integer values in your table. Thus, to truncate 0.456789777 cents, you can run the following query:
SELECT CONVERT(INT, round(123.456789777, 0));The output is displayed as follows:
Use the CONVERT function in the WHERE clause
The CONVERT function can also be used with the WHERE clause in SQL Server. However, it is recommended that you do not wrap the column name into the function because in this case an index assigned to this column won’t be used. Instead, you need to wrap the condition into the function based on which you get the result.
Let’s convert a string specifying the date to the DateTime value and filter the result by the ShippedDate column in the Sales.Order table. To do that, in the WHERE clause, we’ll use the CONVERT function in the condition as follows:
SELECT
o.OrderId
,o.OrderDate
,o.ShippedDate
,o.StoreId
FROM Sales.[Order] o
WHERE o.ShippedDate > CONVERT(DATETIME, '30-Apr-21', 11)
ORDER BY o.ShippedDate ASC;
As you can see, the result has been filtered according to the specified condition in the WHERE clause.
Alternatives to the CONVERT function
When dealing with data types, you can use alternatives to the CONVERT function that may better reach your goals.
The alternatives to the CONVERT function may include the following functions:
- CAST
- FORMAT
- PARSE
CAST function
The CAST function converts a value of any data type into the specified data type. The CAST function is similar to the CONVERT function, but there is a slight difference.
As it has been mentioned, with the CONVERT function, you can simultaneously convert the data type and define the way to do that using a style argument. As for the CAST function, this cannot be done. Also, the CAST function is supported by the ANSI SQL standard, while the CONVERT function is a specific SQL Server function.
It should be noted that if you work with different database management systems, the CAST function will be used.
The syntax of the CAST function is as follows:
CAST(expression AS datatype(length))The table describes the arguments used in the CAST function.
| Argument | Description |
| expression | A valid value to be converted. |
| data_type | A data type of expression you want to get in the result. |
| length (optional parameter) | An integer that specifies the length of the target data type. |
Let’s see in the example how the CAST function works.
In the output, you can see the products that have ’25’ as the first two digits in the price that has been converted to integer values.
FORMAT function
The FORMAT function returns a string value in the specified data type. Preferably, it can be used to format date/time and numeric string values.
The syntax of the FORMAT function is the following:
FORMAT (value, format [, culture])The table describes the arguments used in the FORMAT function.
| Argument | Description |
| value | A valid value to be converted. |
| format | The NVARCHAR format of the value you want to get in the result. The argument should include a valid .NET format string in the NVARCHAR data type. |
| culture (optional argument) | A string that specifies the culture of the target data type. Keep in mind that the culture must be supported by the .Net framework; otherwise, an error occurs. |
In the working example, we’ll format the value of the OrderDate column in the Sales.Order table.
As you can see, the query returned the formatted date in the Great Britain English date format dd/MM/yyyy.
PARSE function
The PARSE function returns a string value of an expression in either date/time or numeric data type.
The syntax of the PARSE function is as follows:
PARSE ( string_value AS data_type [ USING culture ] )The table describes the arguments used in the PARSE function.
| Argument | Description |
| string_value | A valid nvarchar value to be parsed into. If it is invalid, an error occurs. |
| data_type | A data type that you want to get in the result. You can use the Microsoft documentation to view the supported data_type parameters along with styles. |
| culture (optional argument) | A string that specifies the culture which the target data type is parsed into. You can use any valid culture supported by the .Net framework; otherwise, it fails. |
In the example below, we have converted the string value 31-Mar-17 from the Sales.Order table and applied the DATETIME2 style to the value.
Comparison of the CONVERT, CAST, FORMAT, and PARSE functions
Here is a short comparison table of the CONVERT, CAST, FORMAT, and PARSE functions.
| Item to be compared | CONVERT | CAST | FORMAT | PARSE |
| Argument | SQL expression | SQL expression | SQL expression | string |
| Target value | Specified by an argument | Specified by an argument | Specified by an argument | Specified by an argument |
| Style/Culture | Yes | No | Yes | No |
| Supported data types for conversion | Any | Any | Any | From a string to date/time and numeric values |
| Servers in which they work |
|
|
|
|
To demonstrate the examples with the CONVERT function, we used one of the best tools for database development and administration – dbForge Studio for SQL Server. Though there are a bunch of similar tools, dbForge Studio takes the top place. This IDE makes database development easier and much more convenient. dbForge Studio is designed not only to simplify and optimize your database administration, testing, and deployment but also to allow you to stay productive and efficient while performing routine tasks.
Conclusion
In the article, we have reviewed how to use the CONVERT function in SQL Server and discussed possible alternatives. Also, we explored that converting data from one data type to another with dbForge Studio for SQL Server is simple and enjoyable.
Want to know more about other cutting-edge functionalities of dbForge Studio for SQL Server? Or want to see the tool in action? Do not hesitate to download a free 30-day trial version of the Studio tool!
