SQL Server CONVERT() Function
The CONVERT() function in SQL Server changes a value from one data type to another. This is essential for data manipulation, ensuring that your data is in the correct format for calculations, comparisons, or display.
CONVERT(): Definition and Usage
CONVERT() is a versatile function for data type conversions. You specify the target data type, the value to convert, and optionally a style (especially useful for dates and times). For simpler conversions, the CAST() function offers a similar but sometimes more concise alternative.
Syntax
Syntax
CONVERT(data_type(length), expression, style)
Parameter Values
| Parameter | Description |
|---|---|
data_type(length) |
The target data type (e.g., int, varchar(20), datetime). The length is optional for certain types (like varchar). This is required. |
expression |
The value to be converted. This is required. |
style (Optional) |
A style code that controls formatting, mainly relevant for date and time conversions. See the table below for style codes. |
Date/Time Conversion Styles
| Style | Description | Example |
|---|---|---|
| 0, 100 | mon dd yyyy hh:miAM/PM (Without/With century) | CONVERT(varchar, GETDATE(), 100) |
| 1, 101 | mm/dd/yyyy (Default/US) | CONVERT(varchar, GETDATE(), 101) |
| 2, 102 | yyyy.mm.dd (ANSI/yyyy.mm.dd) | CONVERT(varchar, GETDATE(), 102) |
| 3, 103 | dd/mm/yyyy (ANSI/British/French) | CONVERT(varchar, GETDATE(), 103) |
| 4, 104 | dd.mm.yyyy (German) | CONVERT(varchar, GETDATE(), 104) |
| 5, 105 | dd-mm-yyyy (Italian) | CONVERT(varchar, GETDATE(), 105) |
| 6, 106 | dd mon yyyy | CONVERT(varchar, GETDATE(), 106) |
| 7, 107 | Mon dd, yyyy | CONVERT(varchar, GETDATE(), 107) |
| 8, 108 | hh:mm:ss | CONVERT(varchar, GETDATE(), 108) |
| 9, 109 | mon dd yyyy hh:mi:ss:mmmAM/PM (Default + milliseconds) | CONVERT(varchar, GETDATE(), 109) |
| 11, 110 | mm-dd-yyyy (USA) | CONVERT(varchar, GETDATE(), 110) |
| 12, 111 | yyyy/mm/dd (Japan) | CONVERT(varchar, GETDATE(), 111) |
| 13, 112 | yyyymmdd (ISO) | CONVERT(varchar, GETDATE(), 112) |
| 14, 113 | dd mon yyyy hh:mi:ss:mmm (Europe, 24-hour clock) | CONVERT(varchar, GETDATE(), 113) |
| 20, 120 | yyyy-mm-dd hh:mi:ss (ODBC canonical, 24-hour clock) | CONVERT(varchar, GETDATE(), 120) |
| 21, 121 | yyyy-mm-dd hh:mi:ss.mmm (ODBC canonical, 24-hour clock) | CONVERT(varchar, GETDATE(), 121) |
| 126 | yyyy-mm-ddThh:mi:ss.mmm (ISO8601) | CONVERT(varchar, GETDATE(), 126) |
| 127 | yyyy-mm-ddThh:mi:ss.mmmZ (ISO8601 with timezone Z) | CONVERT(varchar, GETDATE(), 127) |
| 130, 131 | Hijri date formats | CONVERT(varchar, GETDATE(), 130/131) |
Float to Real Conversion Styles
| Style | Description |
|---|---|
| 0 | Maximum 6 digits (default) |
| 1 | 8 digits |
| 2 | 16 digits |
Money to Character Conversion Styles
| Style | Description |
|---|---|
| 0 | No comma delimiters, 2 decimal places |
| 1 | Comma delimiters, 2 decimal places |
| 2 | No comma delimiters, 4 decimal places |
Examples
Converting to Integer
Converting a floating-point number to an integer. The decimal part is truncated.
Syntax
SELECT CONVERT(int, 25.65);
Output
25
Converting to VARCHAR
Converting a number to a string.
Syntax
SELECT CONVERT(varchar, 25.65);
Output
25.65
Converting a String to DATETIME
Converting a date string to a datetime value.
Syntax
SELECT CONVERT(datetime, '2017-08-25');
Output
2017-08-25 00:00:00.000
Converting a Datetime to VARCHAR with Style
Converting a datetime to a specific string format using style 101 (mm/dd/yyyy).
Syntax
SELECT CONVERT(varchar, '2017-08-25', 101);
Output
08/25/2017