SQL Convert Function Examples and Alternatives

By: Ben Snaidero   |   Updated: 2022-01-20   |   Comments (1)   |   Related: 1 | 2 | 3 | > Functions System

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.

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;

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;

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;

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;

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;

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;

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>');

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];

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);

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

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.

• Format SQL Server Dates with Format Function
• SQL Server Format Function for Dates and Numbers
• Format numbers in SQL Server

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.

Next Steps

• Read more information on the CONVERT function
  • Date and Time Conversions Using SQL Server
  • SQL Convert Date to YYYY-MM-DD hh
• Performance Comparison of the SQL Server PARSE,CAST,CONVERT and TRY_PARSE,TRY_CAST,TRY_CONVERT Functions
• Read more information on TRY_CONVERT as this function will avoid errors when data fails to convert
• Check out these additional tutorials
  • Learn how to convert data with SQL CAST and SQL CONVERT
  • SQL CAST Function for Data Type Conversions

About the author

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

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips