Data type conversion is a fundamental aspect of working with databases, and SQL Server provides two powerful functions to handle this: CAST and CONVERT. Whether you’re transforming data for reporting, ensuring compatibility across different data types, or simply manipulating data within your queries, understanding CAST and CONVERT is crucial. This guide dives deep into these functions, offering a comprehensive look at their syntax, styles, and best practices to effectively manage data type conversions in SQL Server.
Understanding CAST and CONVERT Syntax in SQL Server
Both CAST and CONVERT functions in SQL Server are designed to explicitly convert an expression from one data type to another. While they achieve the same core objective, their syntax and capabilities differ slightly, offering flexibility depending on your specific needs.
CAST Syntax: Simplicity and Standard Compliance
The CAST function follows the ANSI SQL standard for data type conversion, providing a straightforward and widely recognized syntax.
CAST ( expression AS data_type [ ( length ) ] )- expression: This is the value or column you want to convert. It can be any valid SQL Server expression.
- data_type: This specifies the target data type you want to convert the expression to. Examples include
INT,VARCHAR,DATETIME, and more. You can also use data types likeXML,BIGINT, andSQL_VARIANT. Alias data types are not permitted. - length (optional): For certain data types like
VARCHAR,CHAR,BINARY, andVARBINARY, you can optionally specify the length of the target data type. If omitted, a default length of 30 is often assumed, but it’s best practice to explicitly define the length when needed to avoid unexpected truncation.
CONVERT Syntax: Style and Flexibility
The CONVERT function, while also performing data type conversion, offers additional control through the style argument, especially useful for date and time conversions.
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )- data_type [ ( length ) ]: Similar to
CAST, this defines the target data type and optional length. - expression: The value or column to be converted.
- style (optional): This integer expression dictates how
CONVERTshould translate the expression, particularly for date, time, float, money, and binary data types. The available styles vary based on thedata_type. Ifstyleis NULL, the function returns NULL.
Understanding Data Type Conversions in SQL Server: A visual guide illustrating explicit and implicit conversions between various SQL Server data types.
Exploring Data and Time Styles in SQL Server CONVERT
The CONVERT function truly shines when dealing with date and time data types due to its style argument. Styles allow you to format dates and times in various regional and international standards. Here’s a detailed look at some common date and time styles:
| Style | Without Century (yy) | With Century (yyyy) | Standard | Input/Output Format |
|---|---|---|---|---|
| 0 | – | 100 | Default | 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 | 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 | 113 | Europe default + milliseconds | dd mon yyyy hh:mi:ss:mmm (24-hour) |
| 14 | 14 | 114 | – | hh:mi:ss:mmm (24-hour) |
| 20 | 20 | 120 | ODBC canonical | yyyy-mm-dd hh:mi:ss (24-hour) |
| 21 | 21 or 25 | 121 | ODBC canonical (with milliseconds) | yyyy-mm-dd hh:mi:ss.mmm (24-hour) |
| 22 | 22 | – | U.S. | mm/dd/yy hh:mi:ss AM (or PM) |
| 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 |
Important Considerations for Date and Time Styles:
- Two-Digit Years: SQL Server interprets two-digit years based on a cutoff year of 2049 by default. Years 49 and below are considered 2049, while 50 and above are treated as 1950. Always use four-digit years (yyyy) to avoid ambiguity.
- Nondeterministic Styles: Styles below 100 (excluding 20 and 21), 106, 107, 109, 113, and 130 are nondeterministic. This means the output might vary based on language and regional settings. It’s generally recommended to use deterministic styles (100 and above where available) for consistency.
- XML Compatibility: Style 126 is specifically designed for XML usage, ensuring date and time formats are compatible with XML standards.
- Time Zones: Style 127 includes time zone information in the ISO8601 format, crucial for applications dealing with data across different time zones.
Style Options for Float, Real, Money, and XML Conversions
Beyond date and time, CONVERT‘s style argument extends its formatting capabilities to other data types:
Float and Real Styles
| Style Value | Output |
|---|---|
| 0 (default) | Maximum 6 digits, scientific notation when appropriate. |
| 1 | Always 8 digits, always in scientific notation. |
| 2 | Always 16 digits, always in scientific notation. |
| 3 | Always 17 digits, lossless conversion. Distinct float/real to string guarantee. |
Money and Smallmoney Styles
| Style Value | Output |
|---|---|
| 0 (default) | No commas, two decimal places (e.g., 4235.98). |
| 1 | Commas every three digits, two decimal places (e.g., 3,510.92). |
| 2 | No commas, four decimal places (e.g., 4235.9819). |
| 126 | Equivalent to style 2 when converting to char(n) or varchar(n). |
XML Styles
| Style Value | Output |
|---|---|
| 0 (default) | Default parsing, discards insignificant whitespace, no internal DTD subset. |
| 1 | Preserves insignificant whitespace. Matches xml:space="preserve" behavior. |
| 2 | Enables limited internal DTD subset processing (attribute defaults, internal entity references, DTD content model syntax check). Ignores external DTD subsets and XML declaration standalone attribute. |
| 3 | Preserves insignificant whitespace and enables limited internal DTD subset processing. |
Binary Styles
| Style Value | Output |
|---|---|
| 0 (default) | ASCII characters to binary bytes or vice-versa, 1:1 conversion. Binary data types prepend “0x”. |
| 1, 2 | For binary conversion, expression must be character-based with even hexadecimal digits (0-9, A-F, a-f). Style 1 requires “0x” prefix. Errors for odd digits or invalid characters. Result truncation if target data_type is shorter. Fixed-length types pad with zeros. Character to binary conversion converts each binary character to two hexadecimal characters. Truncation if target is too short. Fixed-size character types pad with spaces to maintain even hex digits. Style 2 does not prepend “0x”. |
Implicit vs. Explicit Conversions: Knowing the Difference
SQL Server performs data type conversions both implicitly and explicitly. Understanding the distinction is key to writing robust and predictable queries.
- Implicit Conversion: SQL Server automatically performs these conversions when it determines it’s necessary, often to resolve data type mismatches in operations like comparisons or assignments. For instance, comparing an
INTto aVARCHARmight trigger an implicit conversion of theVARCHARtoINTif possible. - Explicit Conversion: These conversions are intentionally specified by the user using
CASTorCONVERT. Explicit conversions enhance code readability and prevent unexpected behavior by clearly defining how data types should be transformed.
Data Type Precedence: When implicit conversions occur, SQL Server follows a data type precedence hierarchy to determine the resulting data type. Data types with higher precedence will “dominate” in conversions. For example, if you add an INT and a DECIMAL, the result will be DECIMAL because DECIMAL has higher precedence.
Best Practice: While implicit conversions can be convenient, relying heavily on them can lead to:
- Performance Issues: Implicit conversions can sometimes hinder query performance, especially in large datasets.
- Unexpected Results: SQL Server’s implicit conversion rules might not always align with your intended data transformations, leading to errors or incorrect results.
- Reduced Readability: Implicit conversions make code harder to understand and maintain, as the data type transformations are not explicitly stated.
Therefore, explicit conversions using CAST or CONVERT are generally recommended for clarity, performance predictability, and to maintain control over data transformations.
Handling Large-Value Data Types
SQL Server supports large-value data types like VARCHAR(MAX), NVARCHAR(MAX), and VARBINARY(MAX) for storing data exceeding the limits of traditional data types. CAST and CONVERT handle these types with some specific behaviors:
- Implicit Conversion to Smaller Types: Converting from a large-value type to a smaller counterpart (e.g.,
VARCHAR(MAX)toVARCHAR(50)) is implicit, but be aware of potential truncation if the data exceeds the smaller type’s capacity. - Implicit Conversion from Smaller Types: Converting from
VARCHAR,NVARCHAR, orVARBINARYto theirMAXcounterparts is always implicit and safe. - Explicit Conversion with
SQL_VARIANT: Conversion fromSQL_VARIANTto large-value types is explicit. However, large-value types cannot be implicitly or explicitly converted toSQL_VARIANT. TEXT,NTEXT,IMAGE(Deprecated): Conversions betweenIMAGEandVARBINARY(MAX),TEXTandVARCHAR(MAX), andNTEXTandNVARCHAR(MAX)are implicit for backward compatibility. However,TEXT,NTEXT, andIMAGEare deprecated, and it’s highly recommended to useVARCHAR(MAX),NVARCHAR(MAX), andVARBINARY(MAX)instead.
Working with XML Data Type in Conversions
SQL Server’s native XML data type offers powerful capabilities for managing XML data. CAST and CONVERT play a role in XML data type conversions:
- Serialization: When you convert XML data to string or binary types, SQL Server serializes the XML content according to specific rules.
- Whitespace Handling: The
styleargument inCONVERTcontrols whitespace handling during XML conversion (styles 0, 1, and 3). Style 0 (default) discards insignificant whitespace. Style 1 and 3 preserve whitespace. - DTD Processing: Style 2 and 3 in
CONVERTenable limited internal DTD subset processing for non-validating XML parsing.
Text and Image Data Type Conversion Limitations
The older TEXT and IMAGE data types have limited conversion capabilities:
- No Automatic Conversion: They do not support automatic data type conversion.
- Explicit Conversion with Length Limits: You can explicitly convert
TEXTto character data andIMAGEtoBINARYorVARBINARY, but the maximum length is restricted to 8000 bytes. - Error on Incorrect Conversion: Attempting invalid conversions (e.g., converting character data with letters to
INT) will result in SQL Server errors.
Collation Considerations in String Conversions
Collation settings define the rules for character sorting and comparison. When CAST or CONVERT outputs a character string from a character string input, the output inherits the input’s collation. If the input is not a character string, the output uses the database’s default collation.
Changing Collation: To apply a different collation to the output, use the COLLATE clause with the CAST or CONVERT result:
SELECT CAST('abc' AS VARCHAR(5)) COLLATE French_CS_AS;Truncation and Rounding Behavior in Conversions
Data type conversions can lead to truncation or rounding, especially when converting to data types with smaller sizes or fewer decimal places.
Truncation: Occurs when converting character or binary expressions to shorter data types. SQL Server truncates from the right side.
Rounding: Happens when converting numeric types to types with fewer decimal places. SQL Server’s rounding behavior varies:
| From Data Type | To Data Type | Behavior |
|---|---|---|
NUMERIC |
NUMERIC |
Round |
NUMERIC |
INT |
Truncate |
NUMERIC |
MONEY |
Round |
MONEY |
INT |
Round |
MONEY |
NUMERIC |
Round |
FLOAT |
INT |
Truncate |
FLOAT |
NUMERIC |
Round |
FLOAT |
DATETIME |
Round |
DATETIME |
INT |
Round |
Example of Rounding and Truncation:
SELECT CAST(10.6496 AS INT) AS trunc1,
CAST(-10.6496 AS INT) AS trunc2,
CAST(10.6496 AS NUMERIC) AS round1,
CAST(-10.6496 AS NUMERIC) AS round2;| trunc1 | trunc2 | round1 | round2 |
|---|---|---|---|
| 10 | -10 | 11 | -11 |
Important Note: Avoid constructing binary values and then converting them to numeric types. SQL Server does not guarantee consistent DECIMAL or NUMERIC to BINARY conversion results across different SQL Server versions.
Nondeterministic Datetime Conversions: Be Aware
Certain date and time styles in CONVERT are nondeterministic, meaning their output can vary based on server settings like language. These styles are generally those below 100 (except 20 and 21), and styles 106, 107, 109, 113, and 130. For reliable and consistent date and time conversions, especially in internationalized applications, use deterministic styles (like ISO 8601 formats – styles 126 and 127) or styles 100 and above where available.
Supplementary Characters (Surrogate Pairs) and CAST/CONVERT
Starting with SQL Server 2012, CAST and CONVERT handle supplementary characters (characters outside the basic multilingual plane) in NCHAR and NVARCHAR conversions gracefully. When truncating NCHAR or NVARCHAR values, SQL Server will truncate before a surrogate pair, preventing data corruption and ensuring that supplementary characters remain intact as long as there is enough space.
Compatibility Levels and Default Styles for DATETIME2 and TIME
In earlier SQL Server versions, CAST and CONVERT used style 121 as the default for TIME and DATETIME2 data types, except in computed columns where style 0 was the default. However, in compatibility level 110 and higher, the default style for CAST and CONVERT with TIME and DATETIME2 is always 121, including computed columns.
If your legacy applications depend on the old behavior (style 0 for computed columns), you have two options:
- Lower Compatibility Level: Set the database compatibility level to less than 110.
- Explicit Style: Modify your queries to explicitly specify style 0 in
CASTorCONVERTwhere needed.
Compatibility Level and Default Styles:
| Compatibility Level | Default Style for CAST and CONVERT |
Default Style for Computed Columns |
|---|---|---|
| < 110 | 121 | 0 |
| >= 110 | 121 | 121 |
Upgrading Compatibility Level: Increasing the database compatibility level does not automatically change existing data stored on disk. If you have data that was created using style 0 in computed columns, you’ll need to manually update it to conform to style 121 if desired.
Practical Examples of CAST and CONVERT in SQL Server
Let’s explore several examples demonstrating the usage of CAST and CONVERT in various scenarios.
Example 1: Basic Conversion with CAST and CONVERT
-- Using CAST
SELECT SUBSTRING(Name, 1, 30) AS ProductName,
ListPrice
FROM Production.Product
WHERE CAST(ListPrice AS INT) LIKE '3%';
-- Using CONVERT
SELECT SUBSTRING(Name, 1, 30) AS ProductName,
ListPrice
FROM Production.Product
WHERE CONVERT(INT, ListPrice) LIKE '3%';These queries demonstrate converting the ListPrice column (presumably a numeric type) to an INT to enable using the LIKE operator for string-based pattern matching on the integer representation of the price. Both CAST and CONVERT achieve the same result here.
Example 2: Arithmetic Operations with CAST
SELECT CAST(ROUND(SalesYTD / CommissionPCT, 0) AS INT) AS Computed
FROM Sales.SalesPerson
WHERE CommissionPCT != 0;This example calculates a computed value by dividing SalesYTD by CommissionPCT, rounding the result to the nearest whole number, and then explicitly casting it to an INT. CAST ensures the final Computed value is an integer data type.
Example 3: String Concatenation with CAST
SELECT 'The list price is ' + CAST(ListPrice AS VARCHAR(12)) AS ListPriceText
FROM dbo.DimProduct
WHERE ListPrice BETWEEN 350.00 AND 400.00;Here, CAST is essential to concatenate a string literal with the numeric ListPrice column. ListPrice is converted to VARCHAR(12) before being concatenated, as SQL Server does not implicitly concatenate strings with numeric types directly.
Example 4: Readability Enhancement with CAST
SELECT DISTINCT CAST(EnglishProductName AS CHAR(10)) AS Name,
ListPrice
FROM dbo.DimProduct
WHERE EnglishProductName LIKE 'Long-Sleeve Logo Jersey, M';This example uses CAST to truncate the EnglishProductName to CHAR(10) for display purposes, making the output more concise and readable in the Name column.
Example 5: Filtering with LIKE after Conversion
SELECT p.FirstName,
p.LastName,
s.SalesYTD,
s.BusinessEntityID
FROM Person.Person AS p
INNER JOIN Sales.SalesPerson AS s
ON p.BusinessEntityID = s.BusinessEntityID
WHERE CAST(CAST(s.SalesYTD AS INT) AS CHAR(20)) LIKE '2%';This query demonstrates a nested CAST. First, SalesYTD (likely MONEY or DECIMAL) is cast to INT, and then the INT value is cast to CHAR(20). This double conversion allows the LIKE clause to filter based on the string representation of the integer sales amount.
Example 6: XML Conversion with CONVERT
-- Convert string to typed XML, removing insignificant whitespace
SELECT CONVERT(XML, '<name><fname>Carol</fname><lname>Elliot</lname></name>') AS XML_NoWhitespace;
-- Convert string to typed XML, preserving insignificant whitespace
SELECT CONVERT(XML, '<name><fname>Carol</fname><lname>Elliot</lname></name>', 1) AS XML_PreserveWhitespace;
-- Cast string to typed XML
SELECT CAST('<name><fname>Carol</fname><lname>Elliot</lname></name>' AS XML) AS XML_Cast;These examples illustrate converting strings to the XML data type using both CONVERT and CAST. CONVERT with style 1 demonstrates preserving whitespace, while the default CONVERT and CAST examples show whitespace being removed.
Example 7: Datetime Conversions with CAST and CONVERT
SELECT GETDATE() AS UnconvertedDateTime,
CAST(GETDATE() AS NVARCHAR(30)) AS UsingCast,
CONVERT(NVARCHAR(30), GETDATE(), 126) AS UsingConvertTo_ISO8601;
SELECT '2006-04-25T15:50:59.997' AS UnconvertedText,
CAST('2006-04-25T15:50:59.997' AS DATETIME) AS UsingCast,
CONVERT(DATETIME, '2006-04-25T15:50:59.997', 126) AS UsingConvertFrom_ISO8601;These examples showcase datetime conversions. The first set converts the current date and time to different string representations using CAST and CONVERT (ISO 8601 format with style 126). The second set demonstrates converting a date/time string back to the DATETIME data type using both functions.
Example 8: Binary and Character Data Conversions with CONVERT
-- Binary to Character
SELECT CONVERT(CHAR(8), 0x4E616d65, 0) AS Style0_BinToChar, -- Style 0
CONVERT(CHAR(8), 0x4E616d65, 1) AS Style1_BinToChar, -- Style 1 (truncated)
CONVERT(CHAR(8), 0x4E616d65, 2) AS Style2_BinToChar; -- Style 2
-- Character to Binary
SELECT CONVERT(BINARY(8), 'Name', 0) AS Style0_CharToBin, -- Style 0
CONVERT(BINARY(4), '0x4E616D65', 1) AS Style1_CharToBin, -- Style 1
CONVERT(BINARY(4), '4E616D65', 2) AS Style2_CharToBin; -- Style 2These examples illustrate binary-to-character and character-to-binary conversions using different styles in CONVERT, highlighting the effect of styles on output format and potential truncation.
Example 9: Date, Time, and Datetime Conversions
DECLARE @d1 DATE = GETDATE(),
@t1 TIME = GETDATE(),
@dt1 DATETIME = GETDATE();
SELECT @d1 AS [DATE], CAST(@d1 AS DATETIME) AS [DateToDatetime], -- Date to Datetime
@t1 AS [TIME], CAST(@t1 AS DATETIME) AS [TimeToDatetime], -- Time to Datetime
@dt1 AS [DATETIME], CAST(@dt1 AS DATE) AS [DatetimeToDate], -- Datetime to Date
CAST(@dt1 AS TIME) AS [DatetimeToTime]; -- Datetime to TimeThis example shows conversions between DATE, TIME, and DATETIME data types, demonstrating how SQL Server handles the date and time components during these transformations.
Example 10: Datetime Styles in CONVERT
SELECT CONVERT(NVARCHAR, GETDATE(), 0) AS Style0,
CONVERT(NVARCHAR, GETDATE(), 101) AS Style101,
CONVERT(NVARCHAR, GETDATE(), 126) AS Style126,
-- ... (and so on for other styles)
CONVERT(NVARCHAR, GETDATE(), 131) AS Style131;This example provides a comprehensive list of CONVERT styles for date and time formatting, allowing you to see the output of each style and choose the appropriate one for your needs.
Example 11: Data Type Precedence and Implicit Conversion
DECLARE @string VARCHAR(10) = 1;
SELECT @string + ' is a string.' AS Result; -- Implicit conversion of INT to VARCHAR
DECLARE @notastring INT = '1';
-- SELECT @notastring + ' is not a string.' AS Result; -- Error: VARCHAR to INT conversion fails
SELECT @notastring + 1 AS ResultInt; -- Implicit conversion of CHAR '1' to INTThese examples illustrate data type precedence and implicit conversions. In the first case, the INT ‘1’ is implicitly converted to VARCHAR for concatenation. In the second (commented out) case, SQL Server attempts to implicitly convert the string to INT for addition (due to INT precedence), which fails. The third case shows a successful implicit conversion of a character ‘1’ to an integer 1.
Conclusion: Mastering Data Type Conversion in SQL Server
CAST and CONVERT are indispensable tools for data manipulation in SQL Server. By understanding their syntax, styles, and nuances, you can effectively manage data type conversions, ensuring data integrity, query performance, and code readability. Choosing between CAST and CONVERT often depends on the specific conversion task. CAST provides ANSI-standard simplicity, while CONVERT offers extended formatting control, especially for date, time, and regional data representations. Prioritize explicit conversions to enhance code clarity and avoid potential issues associated with implicit conversions. With a solid grasp of these functions, you’ll be well-equipped to handle diverse data transformation challenges in your SQL Server projects.
Next Steps
To further enhance your knowledge and skills in SQL Server data type conversion, consider exploring these next steps:
- Practice with Different Data Types: Experiment with converting various data types using both
CASTandCONVERTto solidify your understanding. - Explore Advanced CONVERT Styles: Delve deeper into the less common
CONVERTstyles for specific formatting requirements. - Investigate Error Handling: Learn how to handle potential errors during data type conversions, such as truncation or invalid data.
- Performance Tuning: Understand how explicit and implicit conversions can impact query performance and explore optimization techniques.
- Review Official Documentation: Regularly refer to the latest Microsoft SQL Server documentation for the most up-to-date information and best practices regarding
CASTandCONVERT.
By continuously learning and practicing, you’ll become proficient in leveraging CAST and CONVERT to effectively manage data type conversions in SQL Server and build robust, efficient database solutions.
