FB

SQL Server CONVERT Function Guide

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) 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.

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.