CONVERT function in SQL Server

CONVERT function in SQL Server used quite commonly. Here it is What you need to know about CONVERT SQL Server.

Besides utility functions, SQL also has great type conversion functions. They are useful in cases where a query wants input of a particular data type but it receives a different data type. In this case, you need to use the CONVERT function to convert the data type as desired.

Implicit and explicit

The process of changing the data type of a value to another data type is called data type conversion, and nearly every programming language includes some type of function or data conversion function.

When discussing SQL conversion functions in detail, first, you can separate the data conversion process into two parts: implicit and explicit. Implicit conversions are performed by SQL Sever for internal needs. Explicit conversions are performed explicitly by the database programmer or administrator. You will receive help from related functions.

In this article, we will focus on understanding the data type conversion function or CONVERT in SQL Server.

Describe

CONVERT function in SQL Server allows you to convert an expression to any desired data type but possibly in a certain format (especially for date data types). If the conversion fails, CONVERT will report an error, otherwise it will return the corresponding conversion value.

Syntax

To use the CONVERT function in SQL Server, we use the following syntax:

CONVERT(kieudulieu(do_dai), bieuthuc, dinh_dang)

Parameter:

• kieudulieu: The name of the new data type that the expression will be converted to. Can be one of the following types: bigint, int, smallint, tinyint, bit, decimal, numeric, money, smallmoney, float, real, datetime, smalldatetime, char, varchar, text, nchar, nvarchar, ntext, binary, varbinary or image.
• do_dai (optional): data type length for results of char, varchar, nchar, nvarchar, binary and varbinary.
• bieuthuc: value to convert to another data type, which can also be the name of a column in a table or a calculation expression that needs to be converted to a new data type.
• Dinh_dang (optional): is a number that specifies the format for converting data from date to string. The table below describes some commonly used formats in the CONVERT function.

Note:

• When converting a float or numeric data type to an integer, the CONVERT function will cut off the following decimal part.
• See also the functions CAST and TRY_CAST, TRY_CONVERT.
• CONVERT can be used in the following versions of SQL Server: SQL Server 2017, SQL Server 2016, SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, SQL Server 2005.

For example

Take a look and explore some examples of the CONVERT function in SQL Server.

SELECT CONVERT(int, 14.85);
Result: 14 (kết quả cắt phần thập phân phía sau)

SELECT CONVERT(float, 14.85);
Result: 14.85

SELECT CONVERT(varchar, 15.6);
Result: '15.6'

SELECT CONVERT(varchar(4), 15.6);
Result: '15.6'

SELECT CONVERT(float, '15.6');
Result: 15.6

SELECT CONVERT(datetime, '2019-05-02');
Result: '2019-05-02 00:00:00.000'

SELECT CONVERT(varchar, '05/02/2019', 101);
Result: '05/02/2019'

Alternatives to the CONVERT function in SQL Server

CAST function

The CAST function converts a value of any data type to a specific data type. The CAST function is similar to the SQL Server CONVERT function but has a slight difference.

As mentioned, with the CONVERT function you can simultaneously convert data types and specify how to do so using the type argument. For the CAST function, you cannot do this.

The formula of the CAST function is as follows: CAST(expression AS datatype(length))

FORMAT function

The FORMAT function returns a string value in the specified data type. Therefore, it can be used to format dates/times and numeric string values. The formula of the FORMAT function is as follows:

FORMAT (value, format [, culture])

PARSE function

The PARSE function returns the string value of a date/time or numeric expression. The formula of the PARSE function is as follows:

PARSE ( string_value AS data_type [ USING culture ] )

Compare the differences between CONVERT, CAST, FORMAT and PARSE functions

Previous article: CAST function in SQL Server

Next article: TRY_CAST function in SQL Server