FB

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.SQL convert

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)StandardInput/Output
0 or 100Default for DATETIME and SMALLDATETIMEmon dd yyyy hh:miAM (or PM)
1101U.S.1 = mm/dd/yy

101 = mm/dd/yyyy

2102ANSI2 = yy.mm.dd

102 = yyyy.mm.dd

3103British/French3 = dd/mm/yy

103 = dd/mm/yyyy

4104German4 = dd.mm.yy
5105Italian104 = dd.mm.yyyy
61066 = dd mon yy

106 = dd mon yyyy

71077 = Mon dd, yy

107 = Mon dd, yyyy

8 or 24108hh:mi:ss
9 or 109Default + millisecondsmon dd yyyy hh:mi:ss:mmmAM (or PM)
10110U.S.10 = mm-dd-yy

110 = mm-dd-yyyy

11111Japan11 = yy/mm/dd

111 = yyyy/mm/dd

12112ISO12 = yymmdd

112 = yyyymmdd

13 or 113Europe default + millisecondsdd mon yyyy hh:mi:ss:mmm (24h)
14114hh:mi:ss:mmm (24h)
20 or 120ODBC canonicalyyyy-mm-dd hh:mi:ss (24h)
21 or 25 or 121ODBC canonical (with milliseconds)yyyy-mm-dd hh:mi:ss.mmm (24h)
22U.S.mm/dd/yy hh:mi:ss AM (or PM)
23ISO8601yyyy-mm-dd
126ISO8601yyyy-mm-ddThh:mi:ss.mmm
127ISO8601 with time zone Zyyyy-mm-ddThh:mi:ss.mmmZ
130Hijridd mon yyyy hh:mi:ss:mmmAM
131Hijridd/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:

ValueResult
0 (default)Maximum of 6 digits
1Always 8 digits
2Always 16 digits
3Always 17 digits. Every distinct float or real value is guaranteed to convert to a distinct character string.
126, 128, 129Legacy 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:

ValueResultExample
0 (default)No commas every three digits to the left of the decimal. Two digits to the right of the decimal point12345.67
1Commas every three digits to the left of the decimal. Two digits to the right of the decimal point.12,345.67
2No commas every three digits to the left of the decimal. Four digits to the right of the decimal point.12345.6789
126The 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.

FormatSQLOutput
0SELECT CONVERT(VARCHAR, GETDATE(), 0);Jan 9 2019 02:21PM
1SELECT CONVERT(VARCHAR, GETDATE(), 1);01/09/19
101SELECT CONVERT(VARCHAR, GETDATE(), 101);01/09/2019
2SELECT CONVERT(VARCHAR, GETDATE(), 2);19.01.09
102SELECT CONVERT(VARCHAR, GETDATE(), 102;2019.01.09
3SELECT CONVERT(VARCHAR, GETDATE(), 3);09/01/19
103SELECT CONVERT(VARCHAR, GETDATE(), 103);09/01/2019
104SELECT CONVERT(VARCHAR, GETDATE(), 104);09.01.19
105SELECT CONVERT(VARCHAR, GETDATE(), 105);09.01.2019
106SELECT CONVERT(VARCHAR, GETDATE(), 106);09 Jan 2019
107SELECT CONVERT(VARCHAR, GETDATE(), 107);Jan 09, 2019
110SELECT CONVERT(VARCHAR, GETDATE(), 110);01-09-2019
112SELECT CONVERT(VARCHAR, GETDATE(), 112);20190109
113SELECT CONVERT(VARCHAR, GETDATE(), 113);09 Jan 2019 02:21:00:123
126SELECT 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.

Get Your SQL Cheat Sheets Now:

Want Some SQL Cheat Sheets?

sql-cheat-sheet-images-v2

Download the SQL Cheat Sheets: common commands and syntax - to save you time.

You'll get them for Oracle, SQL Server, MySQL, and PostgreSQL.

Print them or use them as an easy reference.