BlackWaspTM

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
1USA date.mm/dd/yy09/08/07
2ANSI date.yy.mm.dd07.09.08
3UK / French date.dd/mm/yy08/09/07
4German 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 separators.mm-dd-yy09-08-07
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 century.mm/dd/yyyy09/08/2007
102ANSI date with century.yyyy.mm.dd2007/09/08
103UK / French date with century.dd/mm/yyyy08/09/2007
104German date with century.dd.mm.yyyy08.09.2007
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 century.mm-dd-yyyy09-08-2007
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:

DECLARE @From FLOAT

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