MSDN Documentation > SQL Server > Development > Functions > Date and Time Functions > CONVERT

CONVERT (Transact-SQL)

Converts an expression of one data type to another data type.

Syntax

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

Parameters

Parameter Description
data_type The target data type to which the expression will be converted. This can be any of the system-supplied data types.
length An optional parameter that specifies the length of the target data type. This is only applicable for certain data types like VARCHAR or NVARCHAR.
expression The expression of the source data type that will be converted. This can be a column name, a literal value, or another expression.
style An optional integer value that specifies how the date is converted from one style to another. This is only applicable when converting to or from date and time data types. Common styles include:
  • 101: USA (mm/dd/yyyy)
  • 103: British/French (dd/mm/yyyy)
  • 112: ISO (yyyymmdd)
  • 120: ODBC canonical (yyyy-mm-dd hh:mi:ss)
  • 121: ODBC canonical (yyyy-mm-dd hh:mi:ss.mmm)
See the official documentation for a full list of styles.

Return Type

Returns the converted expression of the specified data_type.

Description

The CONVERT function is a versatile tool in SQL Server for transforming data from one type to another. This is particularly useful for:

Examples

1. Converting a date to a string in USA format:

SELECT CONVERT(VARCHAR, GETDATE(), 101);
        -- Returns the current date in mm/dd/yyyy format, e.g., '10/26/2023'

2. Converting a numeric value to a string with a specific length:

SELECT CONVERT(VARCHAR(10), 12345.6789);
        -- Returns '12345.6789'

3. Converting a string to a datetime type:

SELECT CONVERT(DATETIME, '2023-10-26 14:30:00');
        -- Returns a datetime value

4. Converting a decimal to an integer:

SELECT CONVERT(INT, 45.67);
        -- Returns 45 (decimal part is truncated)

Tip:

When converting to string types (VARCHAR, NVARCHAR), be mindful of the specified length parameter. If the converted value exceeds the specified length, it will be truncated, potentially leading to data loss or unexpected results.

Related Functions