CONVERT Function

The CONVERT function in SQL Server is used to change the data type of an expression. It offers more flexibility than the CAST function by allowing you to specify a style code for date and time conversions.

Syntax

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

Parameters

data_type: The target data type to convert the expression to. This can be any valid SQL Server data type.
length: An optional parameter specifying the length for character data types.
expression: The value or column to be converted.
style: An optional integer value that specifies the formatting for date and time conversions. Different style codes result in different output formats. For a full list of style codes, please refer to the official Microsoft documentation.

Return Value

Returns the expression converted to the specified data_type.

Description

The CONVERT function is a powerful tool for data manipulation. It's commonly used for:

Converting character strings to numeric or date types.
Converting numeric types to character strings.
Formatting date and time values into specific string representations.
Ensuring compatibility between different data types in operations.

Examples

Example 1: Converting a number to a string

Convert an integer to a character string:

SELECT CONVERT(VARCHAR(20), 12345) AS ConvertedString;

Result:

ConvertedString
    --------------------
    12345

Example 2: Converting a string to a date with style

Convert a string in 'YYYY-MM-DD' format to a datetime data type using style 23:

SELECT CONVERT(DATETIME, '2023-10-27', 23) AS ConvertedDate;

Result:

ConvertedDate
    -----------------------
    2023-10-27 00:00:00.000

Example 3: Converting a date to a formatted string

Convert the current date to a string in 'Month Day, Year' format using style 108 (and time):

SELECT CONVERT(VARCHAR(30), GETDATE(), 108) AS FormattedDateTime;

Result (will vary based on current time):

FormattedDateTime
    ------------------------------
    14:30:55.123

Convert the current date to a string in 'YYYY-MM-DD' format using style 23:

SELECT CONVERT(VARCHAR(10), GETDATE(), 23) AS FormattedDate;

Result (will vary based on current date):

FormattedDate
    ----------
    2023-10-27

Example 4: Converting a decimal to an integer

Convert a decimal number to an integer (truncates decimal part):

SELECT CONVERT(INT, 123.789) AS TruncatedInteger;

Result:

TruncatedInteger
    ----------
    123

CONVERT Function

Syntax

Parameters

Return Value

Description

Examples

Example 1: Converting a number to a string

Example 2: Converting a string to a date with style

Example 3: Converting a date to a formatted string

Example 4: Converting a decimal to an integer

See Also