This web site uses cookies. By using the site you accept the cookie policy.This message is for compliance with the UK ICO law.

SQL Server
SQL 2005+

SQL Server Cast and Convert Functions

The thirty-eighth part of the SQL Server Programming Fundamentals tutorial discusses the cast and convert functions provided by Transact-SQL (T-SQL). These two standard functions allow values to be explicitly converted from one data type to another.

Date Styles

There are a number of date style codes that can be used as the third parameter of the Convert function. They are as follows:

Style CodeStyleFormatExample
0 or 100Default. Equivalent to not specifying a style code.mon dd yyyy hh:mmAMSep 8 2007 9:00PM
3UK / French date.dd/mm/yy08/09/07
5Italian date.dd-mm-yy08-09-07
6Abbreviated month.dd mmm yy08 Sep 07
7Abbreviated month.mmm dd, yySep 08, 07
8 or 10824 hour time.HH:mm:ss21:00:00
9 or 109Default formatting with seconds and milliseconds appended.mon dd yyyy hh:mm:ss:fffAMSep 8 2007 9:00:00:000PM
10USA date with hyphen
11Japanese date.yy/mm/dd07/09/08
12ISO date.yymmdd070908
13 or 113European default with seconds and milliseconds.dd mon yyyy HH:mm:ss:fff08 Sep 2007 21:00:00:000
14 or 11424 hour time with milliseconds.HH:mm:ss:fff21:00:00:000
20 or 120ODBC canonical date and time.yyyy-mm-dd HH:mm:ss2007-09-08 21:00:00
21 or 121ODBC canonical date and time with milliseconds.yyyy-mm-dd HH:mm:ss.fff2007-09-08 21:00:00.000
101USA date with
102ANSI date with
103UK / French date with century.dd/mm/yyyy08/09/2007
104German date with
105Italian date with century.dd-mm-yyyy08-09-2007
106Abbreviated month with century.dd mmm yyyy08 Sep 2007
107Abbreviated month with century.mmm dd, yyyySep 08, 2007
110USA date with hyphen separators and
111Japanese date with century.yyyy/mm/dd2007/09/08
112ISO date with century.yymmdd20070908
126ISO8601, for use in XML.yyy-mm-ddThh:mm:ss2007-09-08T21:00:00

Float / Real Styles

When converting a floating-point value held in a Float or Real data type, three formats are available. The default format (0) returns a string containing a maximum of six significant digits. If required, scientific notation is applied. The other two formats always return a string containing scientific notation and either eight or sixteen digits for the mantissa:


SET @From = 1.23456789

PRINT convert(VARCHAR, @From, 0)    -- 1.23457
PRINT convert(VARCHAR, @From, 1)    -- 1.2345679e+000
PRINT convert(VARCHAR, @From, 2)    -- 1.234567890000000e+000

Money Styles

Three styles are available when converting Money and SmallMoney data to text. These control whether commas are used to separate digit groups and how many decimal places should be included.

Style CodeStyleExample
0Default. No commas and two decimal places.1234567.89
1Commas every three digits of the integer part and two decimal places.1,234,567.89
2No commas and four decimal places.1234567.8901
11 October 2009