CONVERT (SQL Server)

Converts an expression from one data type to another. This is useful for coercing a value into a specific format, such as date or numeric representation.

Syntax

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

Parameters

`data_type`

The target data type to which the expression will be converted.

`length`

(Optional) For character or binary data types, this specifies the length of the target data type.

`expression`

The expression to be converted.

`style`

(Optional) An integer value that specifies how the date or number is formatted. The interpretation of the style parameter depends on the target data_type.

Return Value

Returns the converted value of the specified data_type.

Examples

Example 1: Converting a date to a different format

SELECT CONVERT(varchar, GETDATE(), 101); -- Returns MM/DD/YYYY format
SELECT CONVERT(varchar, GETDATE(), 103); -- Returns DD/MM/YYYY format

Example 2: Converting a numeric value to a string

SELECT CONVERT(varchar, 123.45); -- Returns '123.45'

Example 3: Converting a string to a numeric type

SELECT CONVERT(int, '1234'); -- Returns 1234
SELECT CONVERT(decimal(10,2), '99.50'); -- Returns 99.50

Example 4: Converting a date to a different date type

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

Remarks

The CONVERT function is a Transact-SQL function.
The style parameter for date conversions is an integer that specifies the date format. Common styles include:
- 0 or 100: month day, year (e.g., 10/27/2023)
- 1 or 101: month/day/year (e.g., 10/27/2023)
- 2 or 102: year.month.day (e.g., 2023.10.27)
- 3 or 103: day/month/year (e.g., 27/10/2023)
- 4 or 104: day.month.year (e.g., 27.10.2023)
- 5 or 105: day-month-year (e.g., 27-10-2023)
- 6 or 106: day mon year (e.g., 27 Oct 2023)
- 7 or 107: Mon, day, year (e.g., Oct 27, 2023)
- 8 or 108: HH:MM:SS (24-hour clock)
- 9 or 109: month day, year HH:MM:SS:mmm (e.g., 10/27/2023 10:30:00:123)
- 10 or 110: month-day-year (e.g., 10-27-2023)
- 11 or 111: year/month/day (e.g., 2023/10/27)
- 12 or 112: year-month-day (e.g., 2023-10-27)
- 13 or 113: day month year HH:MM:SS:24:00 (e.g., 27 Oct 2023 10:30:00:000)
- 14 or 114: HH:MM:SS:mmm (24-hour clock)
- 120: YYYY-MM-DD HH:MM:SS (ISO 8601)
- 121: YYYY-MM-DD HH:MM:SS.mmm (ISO 8601 with milliseconds)
When converting to character data types, if the converted value is longer than the target length, the value is truncated.
When converting from a character data type to a numeric data type, the expression must be a valid number. Otherwise, a conversion error will occur.
For a comprehensive list of styles and data type conversions, refer to the official SQL Server documentation.

Note

The CAST function is an ANSI standard function that provides similar functionality to CONVERT. In many cases, CAST is preferred for its readability and adherence to standards.