SQL - CONVERT() Function

The SQL CONVERT() function transforms an expression from one data type to another. If the conversion is successful, the function returns the converted value; otherwise, it produces an error.

With the exception of the style option, the CONVERT() function is equivalent to the CAST() function. While CAST() is a component of ANSI SQL methods, which are accessible in many other databases, CONVERT() is unique to SQL Server and is not.

Syntax

Following is the syntax of the SQL CONVERT() function −

CONVERT(data_type(length), expression, style) 

Parameters

This function accepts only four parameter. The same is described below −

• data_type − The valid expression that to be converted.

• length − It is the length of the resulting datatype which is optional.

• expression − It is the datatype that we want to convert the expression to.

• style − It is the format that used for the result which is optional.

The style value can be any one of the following values, let's look one by one:

Date and time styles for convert −

Float and real styles for convert −

Money and smallmoneystyles for convert −

Example

Let us try to convert the decimal into integer by using the following query −

SELECT CONVERT(int, 123.34) AS Result;

Output

When we execute the above query, the output is obtained as follows −

+--------------------------+
|                   Result |
+--------------------------+
|                      123 |
+--------------------------+

Example

Let us convert the expression into varchar by using the following query −

SELECT CONVERT(varchar, 12344.34) AS varchar;

Output

On executing the above query, the output is displayed as follows −

+--------------------------+
|                  varchar |
+--------------------------+
|                12344.34  |
+--------------------------+

Example

In the following example we are going to convert the expression into the datetime by using the following query −

SELECT CONVERT(datetime, '2023-02-23') AS datetime;

Output

The output for the above query is produced as given below −

+--------------------------+
|                 datetime |
+--------------------------+
|  2023-02-23 00:00:00.000 |
+--------------------------+ 

Example

Look at the following example, where we are going to convert the float into integer by using the following query −

DECLARE @FLOAT AS Float
SET @FLOAT = 12344.567 
SELECT CONVERT(int, @FLOAT) AS ConvertedResult;

Output

If we compile and run the above query, the result is produced as follows −

+------------------------------------+
|                     ConvertedResult|
+------------------------------------+
|                              12344 |
+------------------------------------+

Example

Let’s look into the another scenario, where we are going to change the float value to varchar by using the following query −

DECLARE @FLOAT AS Float
SET @FLOAT = 1235.5687
SELECT CONVERT(varchar, @FLOAT) AS ConvertedResult;

Output

On executing the above query, it will generate the following output as shown below −

+------------------------------------+
|                     ConvertedResult|
+------------------------------------+
|                            1235.57 |
+------------------------------------+

Example

Let’s consider another example, where we are going to convert the money into varchar by running the following query −

DECLARE @MONEY AS MONEY=$1234.56
SELECT CONVERT(Varchar,@MONEY,0) AS Style_0, 
CONVERT(Varchar,@MONEY,1) AS Style_1; 

Output

When the query gets executed, it will generate the output as shown below −

+-----------------------------------+-------------------------------------+
|                  Style_0          |                    Style_1          |
+-----------------------------------+-------------------------------------+
|                  1234.56          |                    1,234.56         |
+-----------------------------------+-------------------------------------+

Example

In the following example, we are going to use the CONVERT() function along with the GETDATE() function to retrieve the current date and time by using the following query −

SELECT CONVERT(VARCHAR(20),GETDATE())AS Result;

Output

When the query gets executed, it will generate the output as shown below −

+------------------------------------+
|                              Result|
+------------------------------------+
|               Feb 23 2023 11:19AM  |
+------------------------------------+ 

Example

Considering the another scenario, where we are going to use the GETDATE() along with a CONVERT() function and mentioning the style by using the following query −

SELECT CONVERT(VARCHAR(11),GETDATE(),106) AS Result;

Output

When the query gets executed, it will generate the output as shown below −

+------------------------------------+
|                              Result|
+------------------------------------+
|                       23 Feb 2023  |
+------------------------------------+