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 −

STYLE STANDARD RESULT
100 datetime and smalldatetime mon dd yyyy hh:miAM (or PM)
101 US mm/dd/yyyy
102 ANSI yyy.mm.dd
103 British/French dd/mm/yyyy
104 German dd.mm.yyyy
105 -- dd mon yyyy
106 -- Mon dd, yyyy
107 -- Mon dd, yyyy
108 -- hh:mi:ss
109 Default + milliseconds mon dd yyyy hh:mi:ss:mmmAM (or PM)
110 USA mm-dd-yyyy
111 Japan yyyy/mm/dd
112 ISO yyyymmdd
113 Europe Default + milliseconds dd mon yyyy hh:mi:ss:mmm (24h)
114 -- hh:mi:ss:mmm (24h)
120 ODBC canonical yyyy-mm-dd hh:mi:ss (24h)
121 ODBC canonical (with milliseconds) default for time, date, datetime2, and datetimeoffset yyyy-mm-dd hh:mi:ss.mmm (24h)
126 ISO8601 yyyy-mm-ddThh:mi:ss.mmm (no spaces)
127 ISO8601 yyyy-MM-ddThh:mm:ss.fffZ (nospaces)

Float and real styles for convert −

STYLE RESULT
0 6 digits max. Use scientific notation, when appropriate.
1 8 digits. Always use scientific notation.
2 16 digits. Always use scientific notation.

Money and smallmoneystyles for convert −

STYLE RESULT
0 No commas and 2 decimals. Example: 28000.54.
1 Commas and 2 decimals. Example: 24,222.63.
2 No commas and 4 decimals. Example: 12345.6789.
126 Equivalent to 2, when converting to char(n) or varchar(n).

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  |
+------------------------------------+
sql-conversion-functions.htm
Advertisements