CONVERT – SQL Server function

SQL CONVERT – In this article you will learn how to use CONVERT function in SQL Server (Transact-SQL) with syntax and examples.

Description OF THE SQL CONVERT FUNCTION

In SQL Server (Transact-SQL), the CONVERT function converts an expression from one data type to another data type.

Syntax OF THE SQL CONVERT FUNCTION

Syntax of the CONVERT function in SQL Server (Transact-SQL):

CONVERT( type [(length) ], [ , style ] )

Parameters or arguments:

• type – the data type into which you want to convert the expression. It can be one of the following: bigint, int, smallint, tinyint, bit, decimal, numeric, money, smallmoney, float, real, datetime, smalldatetime, char, varchar, text, nchar, nvarchar, ntext, binary, varbinary, or image.
• length is optional. The length of the resulting data type for char, varchar, nchar, nvarchar, binary, and varbinary.
• expression – value for conversion to another data type.
• style – optional. The format used to convert between data types, such as date format or string format. This can be one of the following values: Convert datetime to character.

Converting float into real

Converting money into character

Note:

• When converting from float and numeric to integer, the CONVERT function truncates the result. For other conversions CONVERT will round up the result.
• See also the CAST function.

Application
The CONVERT function can be used in the following versions of SQL Server (Transact-SQL):
SQL Server vNext, SQL Server 2016, SQL Server 2015, SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, SQL Server 2005

Example 1:
Let’s look at some examples of the SQL Server function CONVERT to understand how to use the CONVERT function in SQL Server (Transact-SQL). For example:

SELECT CONVERT(int, 15.49);
--Result: 15 (Result truncated)

SELECT CONVERT(float, 15.49);
--Result: 15.49 (Result not truncated)

SELECT CONVERT(varchar, 23.7);
--Result: '23.7'

SELECT CONVERT(varchar(4), 23.7);
--Result: '23.7'

SELECT CONVERT(float, '52.4');
--Result: '52.4'

SELECT CONVERT(datetime, '01.12.2017');
--Result: '2017-12-01 00:00:00.000'

SELECT CONVERT(varchar, '01.12.2017', 101);
--Result: '01.12.2017'

The main difference between the CONVERT function and the CAST function is that the former allows you to format data (e.g., datetime temporal data) when converting them to a character type and specify the format when converting them back. Different integer values of the optional style argument correspond to different types of formats. Let us consider the following example:

SELECT CONVERT(char(25), CONVERT(datetime,'20030722'));

Here we convert the datetime string view to datetime type and then perform the reverse conversion to demonstrate the formatting result. Since the style argument value is not set, the default value (0 or 100) is used. As a result, we will get:

Jul 22 2003 12:00AM

Below are some other values of the style argument and the result obtained in the above example. Note that increasing the style value by 100 results in a four-digit display of the year.

Sql Training Online – Sql Convert Function