SQL Server CAST and CONVERT: Your Comprehensive Guide to Data Type Conversion

In SQL Server, managing data types effectively is crucial for data integrity and query performance. When working with databases, you’ll often encounter situations where you need to change the data type of an expression. This is where the CAST and CONVERT functions come into play. These Transact-SQL functions are essential tools for explicitly converting an expression from one data type to another, ensuring your data is in the format you need for analysis, reporting, or application use.

This guide provides an in-depth look at CAST and CONVERT in SQL Server, detailing their syntax, functionalities, and best practices. Whether you are a database administrator, a developer, or a data analyst, understanding these functions is fundamental for efficient data manipulation in SQL Server.

Understanding the Syntax of CAST and CONVERT

Both CAST and CONVERT are used for data type conversion, but they have slightly different syntaxes.

CAST Syntax

The CAST function follows this syntax:

CAST ( expression AS data_type [ ( length ) ] )

• expression: This is the value or column you want to convert. It can be any valid expression in SQL Server.
• data_type: This specifies the target data type you want to convert the expression to. This can include built-in data types like INT, VARCHAR, DATETIME, as well as more specialized types such as XML, BIGINT, and SQL_VARIANT. Alias data types are not supported with CAST.
• length (optional): For data types that have a length, like VARCHAR or CHAR, you can optionally specify the length of the target data type. If you don’t specify a length, SQL Server uses a default length, which is often 30.

CONVERT Syntax

The CONVERT function offers more flexibility and styling options. Its syntax is:

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

• data_type: Similar to CAST, this is the target data type for the conversion, and you can optionally specify the length.
• expression: The value or column to be converted.
• style (optional): This is an integer expression that dictates how CONVERT should translate the expression, especially for date and time or monetary data types. The available styles vary based on the data_type being converted to. If a NULL style is provided, CONVERT will return NULL.

While both functions achieve data type conversion, CONVERT is often preferred when you need to control the format of date and time values or handle different regional settings through its style parameter.

Data Types Supported in Conversions

CAST and CONVERT support a wide array of SQL Server data types for conversion. These include:

• String Types: CHAR, VARCHAR, NCHAR, NVARCHAR, TEXT, NTEXT
• Numeric Types: INT, BIGINT, SMALLINT, TINYINT, DECIMAL, NUMERIC, FLOAT, REAL, MONEY, SMALLMONEY, BIT
• Date and Time Types: DATE, TIME, DATETIME, SMALLDATETIME, DATETIME2, DATETIMEOFFSET
• Binary Types: BINARY, VARBINARY, IMAGE
• XML Data Type: XML
• Special Types: SQL_VARIANT, UNIQUEIDENTIFIER

It’s important to note that while many conversions are possible, not all are logical or will succeed. For instance, attempting to convert a string containing alphabetic characters to an integer will result in an error.

Date and Time Styles in CONVERT

The CONVERT function shines when dealing with DATE, TIME, DATETIME, and SMALLDATETIME data types because of its style argument. This argument allows you to format date and time values in various predefined formats.

Here’s a table summarizing some of the commonly used date and time styles:

Style	Without Century (yy)	With Century (yyyy)	Standard	Input/Output Format
0	–	100	Default for datetime and smalldatetime	mon dd yyyy hh:miAM (or PM)
1	1	101	U.S.	mm/dd/yy / mm/dd/yyyy
2	2	102	ANSI	yy.mm.dd / yyyy.mm.dd
3	3	103	British/French	dd/mm/yy / dd/mm/yyyy
4	4	104	German	dd.mm.yy / dd.mm.yyyy
5	5	105	Italian	dd-mm-yy / dd-mm-yyyy
6	6	106	–	dd mon yy / dd mon yyyy
7	7	107	–	Mon dd, yy / Mon dd, yyyy
8	8 or 24	108	–	hh:mi:ss
9	–	9 or 109	Default + milliseconds	mon dd yyyy hh:mi:ss:mmmAM (or PM)
10	10	110	USA	mm-dd-yy / mm-dd-yyyy
11	11	111	JAPAN	yy/mm/dd / yyyy/mm/dd
12	12	112	ISO	yymmdd / yyyymmdd
13	–	13 or 113	Europe default + milliseconds	dd mon yyyy hh:mi:ss:mmm (24-hour)
14	14	114	–	hh:mi:ss:mmm (24-hour)
20	–	20 or 120	ODBC canonical	yyyy-mm-dd hh:mi:ss (24-hour)
21	–	21 or 25 or 121	ODBC canonical (with milliseconds)	yyyy-mm-dd hh:mi:ss.mmm (24-hour)
23	–	23	ISO8601	yyyy-mm-dd
126	–	126	ISO8601	yyyy-mm-ddThh:mi:ss.mmm (no spaces)
127	–	127	ISO8601 with time zone Z	yyyy-MM-ddThh:mm:ss.fffZ (no spaces)
130	–	130	Hijri	dd mon yyyy hh:mi:ss:mmmAM
131	–	131	Hijri	dd/mm/yyyy hh:mi:ss:mmmAM

Note: Styles marked with bold in the table are commonly used and recommended for clarity.

Using styles in CONVERT allows you to display dates and times in formats suitable for different regions or specific application requirements.

Float, Real, Money, and XML Styles in CONVERT

Besides date and time, CONVERT also provides styles for FLOAT, REAL, MONEY, SMALLMONEY, and XML data types, though these are less frequently used compared to date styles.

Float and Real Styles

Style Value	Output
0 (default)	Maximum of 6 digits. Scientific notation used when appropriate.
1	Always 8 digits. Always in scientific notation.
2	Always 16 digits. Always in scientific notation.
3	Always 17 digits. Use for lossless conversion. Guarantees every distinct float or real value converts to a distinct character string. (SQL Server 2016 and later, Azure SQL)

Money and Smallmoney Styles

Style Value	Output
0 (default)	No commas every three digits to the left of the decimal, two digits to the right. Example: 4235.98.
1	Commas every three digits to the left of the decimal, two digits to the right. Example: 3,510.92.
2	No commas every three digits to the left of the decimal, four digits to the right. Example: 4235.9819.
126	Equivalent to style 2 when converting to char(n) or varchar(n).

XML Styles

Style Value	Output