SQL Convert Function Examples and Alternatives

By:   |   Updated: 2022-01-20   |   Comments (1)   |   Related: More > TSQL


Problem

There are many different use cases that require us to convert data between different data types (nchar, nvarchar, datetime, datetime2, smalldatetime, etc.) in SQL Server T-SQL, SQL queries, stored procedures, SELECT statements, scripts, etc. Whether it’s converting user input to be loaded into a database column of a certain datatype, formatting data a certain way to be displayed to a user on a UI, or the many other possibilities, the CONVERT function can be used to satisfy any of these requirements.

Solution

In this tutorial we will take an in depth look at the SQL Server CONVERT function as well as discuss possible Transact-SQL alternatives.

What is the CONVERT function?

The CONVERT function is a conversion function that allows you to both convert data between different datatypes and format the result at the same time. Eg. convert text input to a specific date format. It is important to note that the CONVERT function is specific to SQL Server and is not part of the ANSI standard. If you require a function that is platform agnostic then you would be better off using the CAST function as it is compatible with other database platforms.

How to use the CONVERT function?

Before we look at how to use the CONVERT function let us first take a look and explain the syntax of the function and make sure we understand each argument that can be passed to the function.

Syntax

The following outlines the syntax including parameters when using the CONVERT function:

-- CONVERT Syntax: 
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

The expression argument is the value that is to be converted and it can be any valid SQL Server expression. The data_type argument is the target data type and optional length argument can specify the length of the target data type if a user specified length is allowed for that data type. Finally, the style argument is an integer expression that specifies how the function will translate the expression. The range of values for the style argument is determined by the data_type that is specified. A complete list of styles can be found here: Style listing.

Implicit Conversions

There are some data type conversions that do not require the explicit use of the CONVERT (or CAST) function. The following table, taken straight from the Microsoft documentation, illustrates which data type conversions can be done implicitly and which require an explicit conversion.

Implicit Conversions chart

Examples using the CONVERT function

For conversions that do require an explicit call to the CONVERT function the best way to learn how to use the function is to look at examples. Note: All examples below were run (and can be rerun to test) against a SQL Server 2019 (15.0.2000.5) instance using the AdventureWorks 2019 sample database.

This first example shows how you can format a date returned from the getdate() function which is the current date (Note: you could also use a date type column) and convert it to many different styles.

SELECT GETDATE() as "getdate",
       CONVERT(VARCHAR, GETDATE(), 0) as getdate_0,
       CONVERT(VARCHAR, GETDATE(), 2) as getdate_2,
       CONVERT(VARCHAR, GETDATE(), 102) as getdate_102,
       CONVERT(VARCHAR, GETDATE(), 13) as getdate_13,
       CONVERT(VARCHAR, GETDATE(), 120) as getdate_120;
query results using sql server convert function

Depending on the data type you are converting to, the value could also end up being truncated or rounded. These examples show how numeric datatypes can be rounded or truncated. Full details on which data type conversions are truncated or round can be found here.

SELECT  1.32312 as "float",
        CONVERT(INT,CAST(1.32312 AS FLOAT)) as float_truncate_int,
        CONVERT(NUMERIC(9,2),CAST(1.32312 AS FLOAT)) as float_round_numeric,
        CONVERT(FLOAT,CAST(-1.623 AS NUMERIC)) as numeric_round_float;
query results using sql server convert function

This next example adds commas after every 3 digits, which is useful when displaying money typed columns.

SELECT  2332242.23 as "money", CONVERT(VARCHAR,CAST(2332242.23 AS MONEY),1) as money_1;
query results using sql server convert function

This example shows how a decimal value can be displayed using scientific notation.

SELECT  223.532343 as "scientific notation", CONVERT(VARCHAR,CAST(223.532343 AS FLOAT),2) float_2;
query results using sql server convert function

Converting large-value data types can also be affected by truncation. For example, going from text or varchar(max) to a regular varchar column could result in a loss of data if the target data type is not large enough to hold all the data from the character string.

SELECT CAST('This is an example of a large column' AS TEXT) as "text",
       CONVERT(varchar(10),
       CAST('This is an example of a large column' AS TEXT)) as text_varchar10;
query results using sql server convert function

This next example shows how a text string can be converted to binary and vice versa.

SELECT CONVERT(varbinary(32), 'Test Comment', 0) as comment_as_varbinary,
       CONVERT(varchar(32), 0x5465737420436F6D6D656E74, 0) as comment_as_varchar;
query results using sql server convert function

The following example shows how a string can be converted to an XML data type.

SELECT CONVERT(XML, '<root><level1><level2><detail/></level2></level1></root>');
query results using sql server convert function

Finally, this last example shows how you can convert numeric typed columns to text so they can be concatenated with other string values.

SELECT TOP 10 
       'There are ' + CONVERT(varchar,[Quantity]) + 
       ' left in inventory for ProductID:' + CONVERT(varchar,[ProductID]) + 
       ' in LocationID:' + CONVERT(varchar,[LocationID]) as "concatenated string"
FROM [Production].[ProductInventory];
query results using sql server convert function

CONVERT in WHERE Clause Conditions

Just as other functions can be used in WHERE conditions, the CONVERT function is no different. The only thing to be careful of is to not wrap the column name with the function as this will stop an index on the column from being able to be used. Instead wrap the literal value you are searching for with the function. Let’s add an index on the PostTime column in the DatabaseLog table in the AdventureWorks2019 database to see how this can be used. Here is the code to add the index.

CREATE NONCLUSTERED INDEX [IX_DatabaseLog_PostTime] ON [dbo].[DatabaseLog] ([PostTime] ASC);

And using the following query we can convert a string that contains a date value and convert it to a date and use it in a WHERE clause to filter the results from the DatabaseLog table.

SELECT * FROM [dbo].[DatabaseLog]
WHERE PostTime > CONVERT(datetime,'2017-10-27 14:33:17.275',121);
query results using sql server convert function

Looking at the execution plan we can also confirm that it’s using the index that we added.

execution plan

CONVERT Function Alternatives

Here are some alternative options available in SQL Server.

Using CAST

The CAST function in an ANSI standard function that is very similar to the CONVERT function with the only difference being that you can’t format the output with a style at the same time.

FORMAT

The FORMAT function is a function that can be used for locale-aware formatting of datetime and numeric strings.

PARSE

The PARSE function can be used to translate any string value to its datetime or numeric data type equivalent.

Comparison of Functions

The following table shows a comparison of the differences between these three alternative functions and the CONVERT function.

  CONVERT CAST FORMAT PARSE
Argument type SQL expression SQL expression SQL expression String
Return type Specified by argument Specified by argument Specified by argument Specified by argument
Style argument Yes No Yes No
Support conversion between types Any Any Any From string to datetime or number only
Next Steps



Get Started Now - Click here to get your free 14 day trial of SolarWinds Database Insights




get scripts

next tip button



About the author
MSSQLTips author Ben Snaidero Ben Snaidero has been a SQL Server and Oracle DBA for over 10 years and focuses on performance tuning.

View all my tips


Article Last Updated: 2022-01-20

Comments For This Article




Monday, January 24, 2022 - 2:10:17 PM - Joe F Celko Back To Top (89689)
One of the fundamental principles in SQL was that it is a tiered architecture. This means we get the data in the database tier and pass it to one of many possible other tiers that will format it as needed by an application program. Because everything coming out of the database is in one and only one standardized format, your code is portable and not proprietary. Another important programming concept!

But back a few decades ago, with the original Sybase product, we violated this principle all over the place because SQL was brand-new, both as a product and as a concept in programming. In the ANSI/ISO standards, the only statement used convert one data type to another is "CAST(<expression> AS <data type>)" . Various products have various conversion functions, none of which match. But everybody pretty much has CAST() today.

Before anybody asks, I have no idea why we embedded keywords inside parameter list for functions. It does give SQL a particular look that other languages don't have, and it might increase readability, but this requires an LALR(1) one grammar to parse it.


download














get free sql tips
agree to terms