SQL Server CONVERT Function

Summary: in this tutorial, you will learn how to use the SQL Server CONVERT() function to convert a value of one type to another.

Introduction to SQL Server CONVERT() function

The CONVERT() function allows you to convert a value of one type to another.

The following shows the syntax of the CONVERT() function:

CONVERT ( target_type [ ( length ) ] , expression [ , style ] )  
Code language: SQL (Structured Query Language) (sql)

In this syntax:

• target_type is the target data type to which you wan to convert the expression. It includes INT, BIT, SQL_VARIANT, etc. Note that it cannot be an alias data type.
• length is an integer that specifies the length of the target type. The length is optional and defaults to 30.
• expression is a valid expression of any type that will be converted.
• style is an optional integer that determines how the CONVERT() function will translate expression. If style is NULL, the CONVERT() function will return NULL.

The CONVERT() function returns the value of expression translated to the target_type with a specified style.

The CONVERT() is similar to the CAST() function. However, it is specific to SQL Server. In contrast, the CAST() function is a part of ANSI-SQL functions, which is widely available in many other database products.

SQL Server CONVERT() function examples

Let’s take some examples of using the CONVERT() function.

A) Using the CONVERT() function to convert a decimal to an integer example

This example uses the CONVERT() function to convert the decimal number 9.95 to an integer:

SELECT CONVERT(INT, 9.95) result;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

result
-----------
9

(1 row affected)
Code language: SQL (Structured Query Language) (sql)

B) Using the CONVERT() function to convert a decimal to another decimal with different length example

This example uses the CONVERT() function to convert the decimal number 9.95 to another decimal number with zero scales:

SELECT CAST(9.95 AS DEC(2,0)) result;
Code language: SQL (Structured Query Language) (sql)

The output is as follows:

result
-----------
10

(1 row affected)
Code language: SQL (Structured Query Language) (sql)

Notice that the rounding and truncation behaviors of the CONVERT() function are the same as the CAST() functions’.

C) Using the CONVERT() function to convert a string to a datetime value example

This example uses the CONVERT() function to convert the string '2019-03-14' to a datetime value:

SELECT 
    CONVERT(DATETIME, '2019-03-14') result;
Code language: SQL (Structured Query Language) (sql)

The output is:

result
-----------------------
2019-03-14 00:00:00.000

(1 row affected)
Code language: SQL (Structured Query Language) (sql)

D) Using the CONVERT() function to convert a datetime value to a string value example

This example uses the CONVERT() function to convert the current date and time to a string with a specific style:

SELECT 
    CONVERT(VARCHAR, GETDATE(),13) result;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

result
------------------------------
14 Mar 2019 08:59:01:380

(1 row affected)
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the SQL Server CONVERT() function to convert a value from one type to another.