Do you need to convert data in SQL Server? Looking for the format codes for the CONVERT function? Learn all about the SQL Server CONVERT function in this guide.
What Is the SQL Server CONVERT Function?
The CONVERT function in SQL Server converts a value from one data type to another.
You can specify the format to convert to, as well as the data type.
It’s a very useful function, especially for converting dates to strings.
Function Syntax
The syntax of the SQL CONVERT function is:
CONVERT (data_type [ (length) ] ,expression [ ,style ] ) The parameters that the CONVERT function takes are:
- expression: the expression (e.g. column, literal value, function call) to convert.
- data_type: the data type to convert the expression to.
- length: an optional integer that represents the length of the target value. By default, this value is 30.
- style: An integer value that represents how the function will translate the expression.
SQL Server Date Format Styles
The style parameter of the CONVERT function can take a number that represents a specific format. When converting a date value to a varchar value, then the output will be in the format mentioned here.
This table shows the numbers that are used for the style parameter. Many formats are similar but they either show the century of the year (in “yyyy” format) or don’t show the century (in “yy” format).
| Without Century (yy) | With Century (yyyy) | Standard | Input/Output |
| – | 0 or 100 | Default for DATETIME and SMALLDATETIME | mon dd yyyy hh:miAM (or PM) |
| 1 | 101 | U.S. | 1 = mm/dd/yy
101 = mm/dd/yyyy |
| 2 | 102 | ANSI | 2 = yy.mm.dd
102 = yyyy.mm.dd |
| 3 | 103 | British/French | 3 = dd/mm/yy
103 = dd/mm/yyyy |
| 4 | 104 | German | 4 = dd.mm.yy |
| 5 | 105 | Italian | 104 = dd.mm.yyyy |
| 6 | 106 | 6 = dd mon yy
106 = dd mon yyyy |
|
| 7 | 107 | 7 = Mon dd, yy
107 = Mon dd, yyyy |
|
| 8 or 24 | 108 | hh:mi:ss | |
| – | 9 or 109 | Default + milliseconds | mon dd yyyy hh:mi:ss:mmmAM (or PM) |
| 10 | 110 | U.S. | 10 = mm-dd-yy
110 = mm-dd-yyyy |
| 11 | 111 | Japan | 11 = yy/mm/dd
111 = yyyy/mm/dd |
| 12 | 112 | ISO | 12 = yymmdd
112 = yyyymmdd |
| – | 13 or 113 | Europe default + milliseconds | dd mon yyyy hh:mi:ss:mmm (24h) |
| 14 | 114 | hh:mi:ss:mmm (24h) | |
| – | 20 or 120 | ODBC canonical | yyyy-mm-dd hh:mi:ss (24h) |
| – | 21 or 25 or 121 | ODBC canonical (with milliseconds) | yyyy-mm-dd hh:mi:ss.mmm (24h) |
| 22 | U.S. | mm/dd/yy hh:mi:ss AM (or PM) | |
| 23 | ISO8601 | yyyy-mm-dd | |
| 126 | ISO8601 | yyyy-mm-ddThh:mi:ss.mmm | |
| 127 | ISO8601 with time zone Z | yyyy-mm-ddThh:mi:ss.mmmZ | |
| 130 | Hijri | dd mon yyyy hh:mi:ss:mmmAM | |
| 131 | Hijri | dd/mm/yyyy hh:mi:ss:mmmAM |
How does SQL Server handle two-digit years? For example, if you specify the year 80, does that represent 1980 or 2080?
SQL Server bases the dates on a cutoff year of 2049. This means:
- A two-digit year 49 is interpreted as 2049
- A two-digit year 50 is interpreted as 1950
This isn’t very flexible or clear (and a two-digit year was a cause of the Y2K panic, for those of us that remember!). So I recommend using four-digit years.
SQL Server Float and Real Styles
If you’re converting data from a float or real data type, you can use one of the following styles:
| Value | Result |
| 0 (default) | Maximum of 6 digits |
| 1 | Always 8 digits |
| 2 | Always 16 digits |
| 3 | Always 17 digits. Every distinct float or real value is guaranteed to convert to a distinct character string. |
| 126, 128, 129 | Legacy values, which may be deprecated in the future |
SQL Server Money and Smallmoney Styles
If you’re converting from a money or smallmoney data type, you can use one of the following styles:
| Value | Result | Example |
| 0 (default) | No commas every three digits to the left of the decimal. Two digits to the right of the decimal point | 12345.67 |
| 1 | Commas every three digits to the left of the decimal. Two digits to the right of the decimal point. | 12,345.67 |
| 2 | No commas every three digits to the left of the decimal. Four digits to the right of the decimal point. | 12345.6789 |
| 126 | The same as style 2 above when converting to CHAR or VARCHAR |
SQL CONVERT Examples
Here are some examples of using the different format styles with the SQL CONVERT function.
Date and Time
These examples are based on the current date and time of Thursday 9 Jan, 2019, 2:21 PM.
| Format | SQL | Output |
| 0 | SELECT CONVERT(VARCHAR, GETDATE(), 0); |
Jan 9 2019 02:21PM |
| 1 | SELECT CONVERT(VARCHAR, GETDATE(), 1); |
01/09/19 |
| 101 | SELECT CONVERT(VARCHAR, GETDATE(), 101); |
01/09/2019 |
| 2 | SELECT CONVERT(VARCHAR, GETDATE(), 2); |
19.01.09 |
| 102 | SELECT CONVERT(VARCHAR, GETDATE(), 102; |
2019.01.09 |
| 3 | SELECT CONVERT(VARCHAR, GETDATE(), 3); |
09/01/19 |
| 103 | SELECT CONVERT(VARCHAR, GETDATE(), 103); |
09/01/2019 |
| 104 | SELECT CONVERT(VARCHAR, GETDATE(), 104); |
09.01.19 |
| 105 | SELECT CONVERT(VARCHAR, GETDATE(), 105); |
09.01.2019 |
| 106 | SELECT CONVERT(VARCHAR, GETDATE(), 106); |
09 Jan 2019 |
| 107 | SELECT CONVERT(VARCHAR, GETDATE(), 107); |
Jan 09, 2019 |
| 110 | SELECT CONVERT(VARCHAR, GETDATE(), 110); |
01-09-2019 |
| 112 | SELECT CONVERT(VARCHAR, GETDATE(), 112); |
20190109 |
| 113 | SELECT CONVERT(VARCHAR, GETDATE(), 113); |
09 Jan 2019 02:21:00:123 |
| 126 | SELECT CONVERT(VARCHAR, GETDATE(), 126); |
2019-01-09T02:21:00.123 |
Conclusion
Using the SQL CONVERT function in SQL Server can help you convert data from one datatype to another. A common use for this is converting a date to a different format, using a varchar target data type and one of the many in-built format styles.