SQL Server CONVERT() Function: Convert DataType

SQL Server CONVERT() function converts an expression of one data type to another data type.

The CONVERT() function is similar to the CAST() function except for the style parameter. The CONVERT() is specific to SQL Server whereas CAST() is a part of ANSI SQL functions, which is available in many other databases.

It is recommended to use the TRY_CONVERT() function over the CONVERT() function to minimize errors.

CONVERT(data_type, expression[, style ])

Parameters

data_type: The target data type to which you want to convert the specified expression. It includes xml, bigint, and sql_variant.

expression: Any valid expression for which you want to convert the data type.

style: This is an optional integer that specifies how the CONVERT() function will translate the given expression. See the complete list of styles for data type conversion.

Return Value

Returns the expression converted to the target data type.

Example 1:

In the following example, the CONVERT() function converts from decimal to integer.

Example: CONVERT()

SELECT CONVERT(int, 22.56) AS Result;

Example 2:

In this example, CONVERT() function is used to convert a date passed as string with different styles. As seen in the result below, the style parameter has no effect on date passed as static string. Date is returned in the same format for both styles.

Example: CONVERT()

SELECT CONVERT(nvarchar(30), '11/23/2022 10:20:45', 110) AS dateUS,
    CONVERT(nvarchar(30), '11/23/2022 10:20:45', 113) AS dateEuro;

In the below example, current date is passed as the input date and date styles 110 (UD date format), style 112 (ISO standard) and 113 (Europe date format) is applied.

Note: Here you can see the different date formats when a date type is passed.

Example: CONVERT()

SELECT CONVERT (nvarchar(30), GETDATE(), 110) AS USdate,
    CONVERT(nvarchar(30), GETDATE(), 112) AS ISODate, 
    CONVERT(nvarchar(30), GETDATE(), 113) AS Eurodate;

Learn more about datetime styles.

Example 4

The following converts float to varchar type.

Example: CONVERT()

SELECT CONVERT (VARCHAR (10), 234.789)