Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) SQL analytics endpoint in Microsoft Fabric Warehouse in Microsoft Fabric
In SQL Server, data conversion is a crucial aspect of database management and manipulation. The CAST and CONVERT functions are Transact-SQL (T-SQL) built-in functions that allow you to convert an expression from one data type to another. Understanding how to effectively use these functions is essential for writing robust and efficient SQL queries. This article provides a comprehensive guide to mastering data type conversion in SQL Server, focusing on CAST and CONVERT functions for optimal database performance and data integrity.
Understanding CAST and CONVERT Syntax in SQL Server
Both CAST and CONVERT functions serve the purpose of data type conversion in SQL Server, but they differ slightly in their syntax and capabilities.
CAST Syntax:
CAST ( expression AS data_type [ ( length ) ] )CONVERT Syntax:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )Transact-SQL syntax conventions
Key Arguments Explained
Let’s break down the arguments for each function:
- expression: This is the value you wish to convert. It can be any valid expression, including column names, literals, or other functions.
- data_type: This specifies the target data type you want to convert the expression to. It can be any valid SQL Server data type, including
xml,bigint, andsql_variant. Note that alias data types are not permitted here. - length (Optional): For data types like
varchar,char,nvarchar,binary, andvarbinary, you can optionally specify the length of the target data type. If length is not specified, a default length of 30 is assumed. - style (CONVERT only, Optional): This integer expression is unique to the
CONVERTfunction and dictates how the conversion should be performed, particularly for date/time and numeric data types. Thestyleargument allows for various formatting options during conversion. If aNULLstyle value is provided,CONVERTwill returnNULL. The valid range of style values depends on thedata_typebeing used.
Return Types of Conversion Functions
Both CAST and CONVERT functions return the expression after it has been successfully translated (converted) to the specified data_type.
Date and Time Conversion Styles in SQL Server
When dealing with date or time data type expressions in CONVERT, the style argument becomes particularly important. It offers a wide array of options to format date and time values during conversion to character strings or when interpreting character strings as date and time values. The following table details the available styles for date and time conversions in SQL Server.
| Without century (yy) 1 | With century (yyyy) | Standard | Input/output 3 |
|---|---|---|---|
| – | 0 or 100 1, 2 | Default for datetime and smalldatetime | mon dd yyyy hh:miAM (or PM) |
| 1 | 101 | U.S. | 1 = mm/dd/yy101 = mm/dd/yyyy |
| 2 | 102 | ANSI | 2 = yy.mm.dd102 = yyyy.mm.dd |
| 3 | 103 | British/French | 3 = dd/mm/yy103 = dd/mm/yyyy |
| 4 | 104 | German | 4 = dd.mm.yy104 = dd.mm.yyyy |
| 5 | 105 | Italian | 5 = dd-mm-yy105 = dd-mm-yyyy |
| 6 | 106 1 | – | 6 = dd mon yy106 = dd mon yyyy |
| 7 | 107 1 | – | 7 = Mon dd, yy107 = Mon dd, yyyy |
| 8 or 24 | 108 | – | hh:mi:ss |
| – | 9 or 109 1, 2 | Default + milliseconds | mon dd yyyy hh:mi:ss:mmmAM (or PM) |
| 10 | 110 | USA | 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 1, 2 | Europe default + milliseconds | dd mon yyyy hh:mi:ss:mmm (24-hour) |
| 14 | 114 | – | hh:mi:ss:mmm (24-hour) |
| – | 20 or 120 2 | ODBC canonical | yyyy-mm-dd hh:mi:ss (24-hour) |
| – | 21 or 25 or 121 2 | ODBC canonical (with milliseconds) default for time, date, datetime2, and datetimeoffset | yyyy-mm-dd hh:mi:ss.mmm (24-hour) |
| 22 | – | U.S. | mm/dd/yy hh:mi:ss AM (or PM) |
| – | 23 | ISO8601 | yyyy-mm-dd |
| – | 126 4 | ISO8601 | yyyy-mm-ddThh:mi:ss.mmm (no spaces) 6 |
| – | 127 8, 9 | ISO8601 with time zone Z | yyyy-MM-ddThh:mm:ss.fffZ (no spaces) 6 |
| – | 130 1, 2 | Hijri 5 | dd mon yyyy hh:mi:ss:mmmAM 7 |
| – | 131 2 | Hijri 5 | dd/mm/yyyy hh:mi:ss:mmmAM |
1 These style values can return nondeterministic results. This includes all two-digit year (yy) styles and a subset of four-digit year (yyyy) styles.
2 Default styles (0, 9, 13, 20, 21, 23, 25, 100, 109, 113, 120, 121) always return the century (yyyy).
3 Input format when converting to datetime; output format when converting to character data.
4 Optimized for XML usage. Refer to the preceding table for output formats when converting from datetime or smalldatetime to character data.
5 Hijri calendar is used with the Kuwaiti algorithm in SQL Server.
6 Milliseconds (mmm) are not displayed if the value is 0. For example, 2022-11-07T18:26:20.000 is displayed as 2022-11-07T18:26:20.
7 In style 130, mon represents a multi-token Hijri Unicode representation of the full month name. Rendering might be incorrect on default US SSMS installations.
8 Supported only when casting from character data to datetime or smalldatetime. Unspecified time components are set to 00:00:00.000, and unspecified date components are set to 1900-01-01 when casting character data representing only date or time components to datetime or smalldatetime.
9 Time zone indicator Z (UTC-0) simplifies mapping XML datetime values with time zone information to SQL Server datetime values without time zone. HH:MM offset (+ or -) indicates other time zones (e.g., 2022-12-12T23:45:12-08:00).
When converting smalldatetime to character data, styles including seconds or milliseconds display zeros in those positions. For conversions from datetime or smalldatetime, use appropriate char or varchar lengths to truncate unwanted date parts.
When converting character data to datetimeoffset using a style that includes time, a time zone offset is appended to the result.
Important Note on Two-Digit Years: By default, SQL Server interprets two-digit years based on a cutoff year of 2049 (year 49 is 2049, year 50 is 1950). Use four-digit years to avoid ambiguity and ensure accurate date interpretation. You can adjust the two digit year cutoff configuration option in SQL Server for legacy systems if needed.
Float and Real Conversion Styles
For float and real data type expressions, the style argument in CONVERT function offers specific formatting control over the output string representation.
| Style Value | Output |
|---|---|
| 0 (default) | Up to 6 digits; scientific notation used when appropriate. |
| 1 | Always 8 digits; always uses scientific notation. |
| 2 | Always 16 digits; always uses scientific notation. |
| 3 | Always 17 digits; lossless conversion guaranteed for distinct float or real values. Applies to: SQL Server 2016 (13.x) and later versions, and Azure SQL Database. |
| 126, 128, 129 | Included for legacy compatibility; avoid using for new development. |
Money and Smallmoney Conversion Styles
The style argument in CONVERT function provides formatting options for money and smallmoney data types, primarily controlling the inclusion of commas and decimal places in the output.
| Style Value | Output |
|---|---|
| 0 (default) | No commas, two digits to the right of the decimal point (e.g., 4235.98). |
| 1 | Commas every three digits, two digits to the right of the decimal point (e.g., 3,510.92). |
| 2 | No commas, four digits to the right of the decimal point (e.g., 4235.9819). |
| 126 | Equivalent to style 2 when converting to char(n) or varchar(n). |
XML Conversion Styles
When converting to or from xml data types, the style argument in CONVERT function dictates how XML data is parsed and serialized.
| Style Value | Output |
|---|---|
| 0 (default) | Default parsing behavior; discards insignificant whitespace and disallows internal DTD subsets. |
| 1 | Preserves insignificant whitespace; matches xml:space="preserve" behavior. |
| 2 | Enables limited internal DTD subset processing: – Applies default attribute values. – Resolves and expands internal entity references. – Checks DTD content model syntax. Ignores external DTD subsets and XML declaration standalone attribute; parses XML as a standalone document. |
| 3 | Preserves insignificant whitespace and enables limited internal DTD subset processing. |
Binary Conversion Styles
For conversions involving binary(n), char(n), varbinary(n), or varchar(n) data types, the style argument in CONVERT function determines the interpretation and formatting of binary and character data.
| Style Value | Output |
|---|---|
| 0 (default) | Translates ASCII characters to binary bytes and vice versa (1:1 conversion). For binary data types, adds 0x prefix. |
| 1, 2 | For binary data types, expression must be a character expression with an even number of hexadecimal digits (0-9, A-F, a-f). Style 1 requires 0x prefix. Errors if odd number of characters or invalid characters are present. Right truncation if result exceeds data_type length. Fixed-length types pad with zeros on the right. Character types require a binary expression, converting each byte to two hex characters. Truncates if result exceeds data_type length. Fixed-size character types pad with spaces on the right to maintain even hex digits. Style 2 omits 0x prefix. |
Implicit vs. Explicit Conversions in SQL Server
SQL Server performs data type conversions either implicitly or explicitly.
- Implicit Conversion: SQL Server automatically converts data types when it’s not explicitly specified, such as during assignments or comparisons.
- Explicit Conversion: Requires using
CASTorCONVERTfunctions to explicitly define the data type conversion.
The following diagram illustrates the allowed implicit and explicit data type conversions in SQL Server.
Diagram showing a table of the possible data type conversions.
Downloadable Resource: This chart is also available as a PNG file from the Microsoft Download Center.
The resulting data type after conversion depends on the context:
- Explicit Conversions: The target data type is determined by the
CASTorCONVERTfunction specified in the statement. - Implicit Conversions:
- Assignment statements (e.g., variable assignment, inserting into a column) result in the data type defined by the variable or column.
- Comparison operators and other expressions follow data type precedence rules to determine the resulting data type.
Tip: See the precedence example later in this article for a practical demonstration of data type precedence in conversions.
When converting between datetimeoffset and character types (char, nchar, nvarchar, varchar), ensure the time zone offset part always has double digits for both hours (HH) and minutes (MM) (e.g., -08:00).
Unicode Considerations: Unicode data always uses an even number of bytes. Exercise caution when converting between binary or varbinary and Unicode data types. For example, SELECT CAST(CAST(0x41 AS nvarchar) AS varbinary); returns 0x4100 instead of 0x41. Refer to Collation and Unicode Support for more information.
Conversion of Large-Value Data Types
Large-value data types (varchar(max), nvarchar(max), varbinary(max)) behave similarly to their smaller counterparts (varchar, nvarchar, varbinary) in terms of implicit and explicit conversions. Key points to consider:
- Implicit conversion between
imageandvarbinary(max),textandvarchar(max), andntextandnvarchar(max). - Implicit conversion from large-value types to smaller types (e.g.,
varchar(max)tovarchar) may result in truncation if the value exceeds the smaller type’s length. - Implicit conversion from
nvarchar,varbinary, orvarcharto their corresponding large-value types. - Explicit conversion required from
sql_variantto large-value types. - Large-value data types cannot be implicitly or explicitly converted to
sql_variant.
For xml data type conversion, see Create Instances of XML Data.
Handling XML Data Type Conversions
Explicit or implicit casting of the xml data type to string or binary data types serializes the XML content according to specific rules. See Define the Serialization of XML Data for details. For conversions from other data types to xml, see Create Instances of XML Data.
Text and Image Data Type Limitations
text and image data types do not support automatic data type conversion. Explicit conversion is possible from text to character data and image to binary or varbinary, but with a maximum length of 8000 bytes. Incorrect conversions, like converting character expressions with letters to int, will result in SQL Server error messages.
Output Collation After Conversion
When CAST or CONVERT functions output a character string from a character string input, the output collation and collation label are the same as the input. If the input is not a character string, the output uses the database’s default collation and is labeled as coercible-default. For more information, see Collation Precedence (Transact-SQL).
To assign a different collation to the output, use the COLLATE clause with the result expression of CAST or CONVERT. Example: SELECT CAST('abc' AS varchar(5)) COLLATE French_CS_AS;
Data Truncation and Rounding During Conversions
Converting character or binary expressions to different data types may lead to truncation, partial display, or errors if the result is too short. Conversions to binary, char, nchar, nvarchar, varbinary, or varchar are truncated, except as noted in the table below.
| From data type | To data type | Result |
|---|---|---|
| int, smallint, or tinyint | char varchar |
Result too short to display |
| nchar nvarchar |
Error 1 | |
| money, smallmoney, numeric, decimal, float, or real | char varchar |
Error 1 |
| nchar nvarchar |
Error 1 |
1 Error returned because result length is insufficient for display.
SQL Server guarantees roundtrip conversions (converting a data type back to its original type) will yield identical values across SQL Server versions. Example:
DECLARE @myval DECIMAL(5, 2);
SET @myval = 193.57;
SELECT CAST(CAST(@myval AS VARBINARY(20)) AS DECIMAL(10, 5));
-- Or, using CONVERT
SELECT CONVERT(DECIMAL(10, 5), CONVERT(VARBINARY(20), @myval));
GOWarning: Avoid constructing
binaryvalues and converting them to numeric data types. SQL Server does not guarantee consistentdecimalornumerictobinaryconversion results across versions.
Example of Result Too Small:
USE AdventureWorks2022;
GO
SELECT
p.FirstName,
p.LastName,
SUBSTRING(p.Title, 1, 25) AS Title,
CAST(e.SickLeaveHours AS CHAR(1)) AS [Sick Leave]
FROM HumanResources.Employee e
INNER JOIN Person.Person p
ON e.BusinessEntityID = p.BusinessEntityID
WHERE NOT e.BusinessEntityID > 5;
GOResult Set:
FirstName LastName Title Sick Leave
--------- ------------- ------- --------
Ken Sanchez NULL *
Terri Duffy NULL *
Roberto Tamburello NULL *
Rob Walters NULL *
Gail Erickson Ms. *
(5 row(s) affected)Rounding Behavior: When converting between data types with different decimal places, SQL Server may truncate or round values.
| From | To | Behavior |
|---|---|---|
| numeric | numeric | Round |
| numeric | int | Truncate |
| numeric | money | Round |
| money | int | Round |
| money | numeric | Round |
| float | int | Truncate |
| float | numeric | Round 1 |
| float | datetime | Round |
| datetime | int | Round |
1 float to decimal or numeric conversion with scientific notation is limited to 17 digits of precision. Higher precision values round to zero.
Example of Truncation and Rounding:
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;Results:
| trunc1 | trunc2 | round1 | round2 |
|---|---|---|---|
| 10 | -10 | 11 | -11 |
When converting to a data type with fewer decimal places, values are rounded. Example: SELECT CAST(10.3496847 AS money); returns $10.3497.
SQL Server returns errors when converting nonnumeric character data to decimal, float, int, numeric. Errors also occur when converting empty strings (” “) to numeric or decimal.
Nondeterministic Datetime Conversions
String-to-datetime conversions are nondeterministic for the following styles:
- All styles below 100 1
- 106
- 107
- 109
- 113
- 130
1 Except for styles 20 and 21.
See Nondeterministic conversion of literal date strings into DATE values for more details.
Supplementary Character (Surrogate Pairs) Handling
Starting with SQL Server 2012 (11.x), CAST operations from nchar or nvarchar to shorter nchar or nvarchar types using supplementary character (SC) collations truncate before surrogate pairs, not within them. Example:
DECLARE @x NVARCHAR(10) = 'ab' + NCHAR(0x10000);
SELECT CAST(@x AS NVARCHAR(3)); -- @x will hold 'ab'CONVERT behaves analogously to CAST with SC collations. See Collation and Unicode Support – Supplementary Characters for more information.
Compatibility Levels and Behavior Changes
In earlier SQL Server versions, default styles for CAST and CONVERT on time and datetime2 were 121, except for computed columns (default style 0). This impacted computed columns in queries with auto-parameterization or constraint definitions.
Compatibility level 110 and higher changed the default style to 121 for CAST and CONVERT on time and datetime2 in all contexts, including computed columns. For queries relying on the older behavior, use compatibility level less than 110 or explicitly specify style 0.
| Compatibility level | Default style for CAST and CONVERT 1 |
Default style for computed columns |
|---|---|---|
| < 110 | 121 | 0 |
| ≥ 110 | 121 | 121 |
1 Except for computed columns in compatibility levels below 110.
Upgrading database compatibility level does not change existing data on disk. Manual correction is needed if data relies on the older style 0 behavior. For example, tables created with SELECT INTO from computed columns might store data in style 0 instead of the computed column definition. Manually update this data to style 121 if needed.
Examples of SQL Server Conversion Functions
A. Basic Usage of CAST and CONVERT
These examples demonstrate retrieving product names where the list price starts with ‘3’, converting ListPrice to int using both CAST and CONVERT.
Using CAST:
USE AdventureWorks2022;
GO
SELECT
SUBSTRING(Name, 1, 30) AS ProductName,
ListPrice
FROM Production.Product
WHERE CAST(ListPrice AS INT) LIKE '3%';
GOUsing CONVERT:
USE AdventureWorks2022;
GO
SELECT
SUBSTRING(Name, 1, 30) AS ProductName,
ListPrice
FROM Production.Product
WHERE CONVERT(INT, ListPrice) LIKE '3%';
GOResult Set (for both CAST and CONVERT):
ProductName ListPrice
------------------------------ ---------------------
LL Road Frame - Black, 58 337.22
LL Road Frame - Black, 60 337.22
LL Road Frame - Black, 62 337.22
LL Road Frame - Red, 44 337.22
LL Road Frame - Red, 48 337.22
LL Road Frame - Red, 52 337.22
LL Road Frame - Red, 58 337.22
LL Road Frame - Red, 60 337.22
LL Road Frame - Red, 62 337.22
LL Road Frame - Black, 44 337.22
LL Road Frame - Black, 48 337.22
LL Road Frame - Black, 52 337.22
Mountain-100 Black, 38 3374.99
Mountain-100 Black, 42 3374.99
Mountain-100 Black, 44 3374.99
Mountain-100 Black, 48 3374.99
HL Road Front Wheel 330.06
LL Touring Frame - Yellow, 62 333.42
LL Touring Frame - Blue, 50 333.42
LL Touring Frame - Blue, 54 333.42
LL Touring Frame - Blue, 58 333.42
LL Touring Frame - Blue, 62 333.42
LL Touring Frame - Yellow, 44 333.42
LL Touring Frame - Yellow, 50 333.42
LL Touring Frame - Yellow, 54 333.42
LL Touring Frame - Yellow, 58 333.42
LL Touring Frame - Blue, 44 333.42
HL Road Tire 32.60
(28 rows affected)B. CAST with Arithmetic Operators
This example calculates Computed value by dividing SalesYTD by CommissionPCT, rounding to the nearest whole number, and then casting to int.
USE AdventureWorks2022;
GO
SELECT
CAST(ROUND(SalesYTD / CommissionPCT, 0) AS INT) AS Computed
FROM Sales.SalesPerson
WHERE CommissionPCT != 0;
GOResult Set:
Computed
-----------
379753754
346698349
257144242
176493899
281101272
0
301872549
212623750
298948202
250784119
239246890
101664220
124511336
97688107
(14 row(s) affected)C. Concatenation Using CAST
This example uses CAST to concatenate non-character expressions in the AdventureWorksDW2022 database.
SELECT
'The list price is ' + CAST(ListPrice AS VARCHAR(12)) AS ListPrice
FROM dbo.DimProduct
WHERE ListPrice BETWEEN 350.00 AND 400.00;Result Set:
ListPrice
------------------------
The list price is 357.06
The list price is 364.09
The list price is 364.09
The list price is 364.09
The list price is 364.09D. Enhancing Text Readability with CAST
This example uses CAST in the SELECT list to convert the Name column to char(10) in AdventureWorksDW2022.
SELECT DISTINCT
CAST(EnglishProductName AS CHAR(10)) AS Name,
ListPrice
FROM dbo.DimProduct
WHERE EnglishProductName LIKE 'Long-Sleeve Logo Jersey, M';
GOResult Set:
Name ListPrice
---------- ---------
Long-Sleev 31.2437
Long-Sleev 32.4935
Long-Sleev 49.99E. Filtering with LIKE Clause and CAST
This example uses CAST to convert the money column SalesYTD to int then to char(20) for use with the LIKE clause.
USE AdventureWorks2022;
GO
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%';
GOResult Set:
FirstName LastName SalesYTD BusinessEntityID
----------------- ------------------- -------------------- ----------------
Tsvi Reiter 2811012.7151 279
Syed Abbas 219088.8836 288
Rachel Valdez 2241204.0424 289
(3 row(s) affected)F. Typed XML Conversion with CONVERT or CAST
These examples demonstrate converting data to typed XML using CONVERT and CAST, leveraging XML Data Type and Columns (SQL Server).
Removing Insignificant Whitespace:
SELECT CONVERT(XML, '<ElementName> Some text </ElementName>')Preserving Insignificant Whitespace:
SELECT CONVERT(XML, '<ElementName> Some text </ElementName>', 1)Casting to Typed XML:
SELECT CAST('<name><fname>Carol</fname><lname>Elliot</lname></name>' AS XML)Refer to Create Instances of XML Data for more XML conversion examples.
G. Datetime Data Conversions with CAST and CONVERT
This example shows converting GETDATE() values to character data using CAST and to ISO 8601 format using CONVERT.
SELECT
GETDATE() AS UnconvertedDateTime,
CAST(GETDATE() AS NVARCHAR(30)) AS UsingCast,
CONVERT(NVARCHAR(30), GETDATE(), 126) AS UsingConvertTo_ISO8601;
GOResult Set:
UnconvertedDateTime UsingCast UsingConvertTo_ISO8601
----------------------- ------------------------------ ------------------------------
2023-10-27 14:30:45.237 Oct 27 2023 2:30PM 2023-10-27T14:30:45.237
(1 row(s) affected)The following example demonstrates the reverse conversion, from character data to datetime using both CAST and CONVERT.
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;
GOResult Set:
UnconvertedText UsingCast UsingConvertFrom_ISO8601
----------------------- ----------------------- ------------------------
2006-04-25T15:50:59.997 2006-04-25 15:50:59.997 2006-04-25 15:50:59.997
(1 row(s) affected)H. Binary and Character Data Conversion with CONVERT
These examples illustrate binary and character data conversions using different styles with CONVERT.
Binary to Character (Style 0):
--Convert the binary value 0x4E616d65 to a character value.
SELECT CONVERT(CHAR(8), 0x4E616d65, 0) AS [Style 0, binary to character];Result Set:
Style 0, binary to character
----------------------------
Name
(1 row(s) affected)Binary to Character (Style 1 – Truncation):
SELECT CONVERT(CHAR(8), 0x4E616d65, 1) AS [Style 1, binary to character];Result Set:
Style 1, binary to character
------------------------------
0x4E616D
(1 row(s) affected)Binary to Character (Style 2 – No Truncation):
SELECT CONVERT(CHAR(8), 0x4E616d65, 2) AS [Style 2, binary to character];Result Set:
Style 2, binary to character
------------------------------
4E616D65
(1 row(s) affected)Character to Binary (Style 0):
SELECT CONVERT(BINARY(8), 'Name', 0) AS [Style 0, character to binary];Result Set:
Style 0, character to binary
----------------------------
0x4E616D6500000000
(1 row(s) affected)Character to Binary (Style 1):
SELECT CONVERT(BINARY(4), '0x4E616D65', 1) AS [Style 1, character to binary];Result Set:
Style 1, character to binary
----------------------------
0x4E616D65
(1 row(s) affected)Character to Binary (Style 2):
SELECT CONVERT(BINARY(4), '4E616D65', 2) AS [Style 2, character to binary];Result Set:
Style 2, character to binary
----------------------------------
0x4E616D65
(1 row(s) affected)I. Date and Time Data Type Conversions
This example demonstrates conversions between date, time, and datetime data types.
DECLARE @d1 DATE, @t1 TIME, @dt1 DATETIME;
SET @d1 = GETDATE();
SET @t1 = GETDATE();
SET @dt1 = GETDATE();
SET @d1 = GETDATE();
-- When converting date to datetime the minutes portion becomes zero.
SELECT @d1 AS [DATE], CAST(@d1 AS DATETIME) AS [date as datetime];
-- When converting time to datetime the date portion becomes zero
-- which converts to January 1, 1900.
SELECT @t1 AS [TIME], CAST(@t1 AS DATETIME) AS [time as datetime];
-- When converting datetime to date or time non-applicable portion is dropped.
SELECT @dt1 AS [DATETIME], CAST(@dt1 AS DATE) AS [datetime as date], CAST(@dt1 AS TIME) AS [datetime as time];Ensure date values are within compatible ranges when converting between date, datetime, and datetime2. datetime has a minimum year of 1753, while date and datetime2 have a minimum year of 0001.
DECLARE @d1 DATE, @dt1 DATETIME , @dt2 DATETIME2
SET @d1 = '1492-08-03' --This is okay; Minimum YYYY for DATE is 0001
SET @dt2 = CAST(@d1 AS DATETIME2) --This is okay; Minimum YYYY for DATETIME2 IS 0001
SET @dt1 = CAST(@d1 AS DATETIME) --This will error with (Msg 242) "The conversion of a date data type to a datetime data type resulted in an out-of-range value."
--Minimum YYYY for DATETIME is 1753J. CONVERT with Datetime Data in Various Formats
This example uses CONVERT to display GETDATE() values in all date and time styles listed in the Date and Time styles section.
| Format # | Example query | Sample result |
|---|---|---|
| 0 | SELECT CONVERT(NVARCHAR, GETDATE(), 0) |
Oct 27 2023 2:30PM |
| 1 | SELECT CONVERT(NVARCHAR, GETDATE(), 1) |
10/27/23 |
| 2 | SELECT CONVERT(NVARCHAR, GETDATE(), 2) |
23.10.27 |
| 3 | SELECT CONVERT(NVARCHAR, GETDATE(), 3) |
27/10/23 |
| 4 | SELECT CONVERT(NVARCHAR, GETDATE(), 4) |
27.10.23 |
| 5 | SELECT CONVERT(NVARCHAR, GETDATE(), 5) |
27-10-23 |
| 6 | SELECT CONVERT(NVARCHAR, GETDATE(), 6) |
27 Oct 23 |
| 7 | SELECT CONVERT(NVARCHAR, GETDATE(), 7) |
Oct 27, 23 |
| 8 or 24 or 108 | SELECT CONVERT(NVARCHAR, GETDATE(), 8) |
14:30:45 |
| 9 or 109 | SELECT CONVERT(NVARCHAR, GETDATE(), 9) |
Oct 27 2023 2:30:45:237PM |
| 10 | SELECT CONVERT(NVARCHAR, GETDATE(), 10) |
10-27-23 |
| 11 | SELECT CONVERT(NVARCHAR, GETDATE(), 11) |
23/10/27 |
| 12 | SELECT CONVERT(NVARCHAR, GETDATE(), 12) |
231027 |
| 13 or 113 | SELECT CONVERT(NVARCHAR, GETDATE(), 13) |
27 Oct 2023 14:30:45:237 |
| 14 or 114 | SELECT CONVERT(NVARCHAR, GETDATE(), 14) |
14:30:45:237 |
| 20 or 120 | SELECT CONVERT(NVARCHAR, GETDATE(), 20) |
2023-10-27 14:30:45 |
| 21 or 25 or 121 | SELECT CONVERT(NVARCHAR, GETDATE(), 21) |
2023-10-27 14:30:45.237 |
| 22 | SELECT CONVERT(NVARCHAR, GETDATE(), 22) |
10/27/23 2:30:45 PM |
| 23 | SELECT CONVERT(NVARCHAR, GETDATE(), 23) |
2023-10-27 |
| 101 | SELECT CONVERT(NVARCHAR, GETDATE(), 101) |
10/27/2023 |
| 102 | SELECT CONVERT(NVARCHAR, GETDATE(), 102) |
2023.10.27 |
| 103 | SELECT CONVERT(NVARCHAR, GETDATE(), 103) |
27/10/2023 |
| 104 | SELECT CONVERT(NVARCHAR, GETDATE(), 104) |
27.10.2023 |
| 105 | SELECT CONVERT(NVARCHAR, GETDATE(), 105) |
27-10-2023 |
| 106 | SELECT CONVERT(NVARCHAR, GETDATE(), 106) |
27 Oct 2023 |
| 107 | SELECT CONVERT(NVARCHAR, GETDATE(), 107) |
Oct 27, 2023 |
| 110 | SELECT CONVERT(NVARCHAR, GETDATE(), 110) |
10-27-2023 |
| 111 | SELECT CONVERT(NVARCHAR, GETDATE(), 111) |
2023/10/27 |
| 112 | SELECT CONVERT(NVARCHAR, GETDATE(), 112) |
20231027 |
| 113 | SELECT CONVERT(NVARCHAR, GETDATE(), 113) |
27 Oct 2023 14:30:45.237 |
| 120 | SELECT CONVERT(NVARCHAR, GETDATE(), 120) |
2023-10-27 14:30:45 |
| 121 | SELECT CONVERT(NVARCHAR, GETDATE(), 121) |
2023-10-27 14:30:45.237 |
| 126 | SELECT CONVERT(NVARCHAR, GETDATE(), 126) |
2023-10-27T14:30:45.237 |
| 127 | SELECT CONVERT(NVARCHAR, GETDATE(), 127) |
2023-10-27T14:30:45.237 |
| 130 | SELECT CONVERT(NVARCHAR, GETDATE(), 130) |
12 ربيع الآخر 1445 2:30:45:237P |
| 131 | SELECT CONVERT(NVARCHAR, GETDATE(), 131) |
12/04/1445 2:30:45:237PM |
K. Data Type Precedence Effects in Conversions
This example demonstrates data type precedence during implicit conversion. Declaring a varchar(10) variable and assigning an integer value results in implicit conversion of the integer to varchar.
DECLARE @string VARCHAR(10);
SET @string = 1;
SELECT @string + ' is a string.' AS ResultResult Set:
Result
-----------------------
1 is a string.
(1 row(s) affected)However, using an int variable and attempting to concatenate a string results in an error because SQL Server tries to implicitly convert the string to int due to int having higher precedence.
DECLARE @notastring INT;
SET @notastring = '1';
SELECT @notastring + ' is not a string.' AS ResultError:
Msg 245, Level 16, State 1, Line 3
Conversion failed when converting the varchar value ' is not a string.' to data type int.Providing a convertible string allows the statement to succeed as the string ‘1’ can be implicitly converted to int.
DECLARE @notastring INT;
SET @notastring = '1';
SELECT @notastring + '1'In this case, the SELECT statement returns 2 as the string '1' is converted to the integer 1, and the + operator performs addition instead of string concatenation.
Examples for Azure Synapse Analytics and Analytics Platform System (PDW)
L. Using CAST and CONVERT in Azure Synapse Analytics/PDW
These examples show CAST and CONVERT usage in Azure Synapse Analytics and Analytics Platform System (PDW), retrieving product names with list prices starting with ‘3’ and converting ListPrice to int.
Using CAST:
SELECT
EnglishProductName AS ProductName,
ListPrice
FROM dbo.DimProduct
WHERE CAST(ListPrice AS int) LIKE '3%';Using CONVERT:
SELECT
EnglishProductName AS ProductName,
ListPrice
FROM dbo.DimProduct
WHERE CONVERT(INT, ListPrice) LIKE '3%';M. Arithmetic Operations with CAST in Azure Synapse Analytics/PDW
This example calculates discounted prices by multiplying UnitPrice by UnitPriceDiscountPct, rounding, and casting to int in Azure Synapse Analytics/PDW.
SELECT
ProductKey,
UnitPrice,
UnitPriceDiscountPct,
CAST(ROUND (UnitPrice*UnitPriceDiscountPct,0) AS int) AS DiscountPrice
FROM dbo.FactResellerSales
WHERE SalesOrderNumber = 'SO47355' AND UnitPriceDiscountPct > .02;Result Set:
ProductKey UnitPrice UnitPriceDiscountPct DiscountPrice
---------- --------- -------------------- -------------
323 430.6445 0.05 22
213 18.5043 0.05 1
456 37.4950 0.10 4
456 37.4950 0.10 4
216 18.5043 0.05 1N. LIKE Clause with CAST in Azure Synapse Analytics/PDW
This example converts ListPrice to int and then char(20) to use with the LIKE clause in Azure Synapse Analytics/PDW.
SELECT
EnglishProductName AS Name,
ListPrice
FROM dbo.DimProduct
WHERE CAST(CAST(ListPrice AS INT) AS CHAR(20)) LIKE '2%';O. Datetime Conversions in Azure Synapse Analytics/PDW with CAST and CONVERT
These examples demonstrate datetime conversions using CAST and CONVERT in Azure Synapse Analytics/PDW.
Using CAST and CONVERT to display current datetime in different formats:
SELECT TOP(1)
SYSDATETIME() AS UnconvertedDateTime,
CAST(SYSDATETIME() AS NVARCHAR(30)) AS UsingCast,
CONVERT(NVARCHAR(30), SYSDATETIME(), 126) AS UsingConvertTo_ISO8601
FROM dbo.DimCustomer;Result Set:
UnconvertedDateTime UsingCast UsingConvertTo_ISO8601
--------------------- --------------------------- ---------------------------
2010-07-20 13:44:31.587 2010-07-20 13:44:31.5879025 2010-07-20T13:44:31.5879025Converting character data to datetime:
SELECT TOP(1)
'2010-07-25T13:50:38.544' AS UnconvertedText,
CAST('2010-07-25T13:50:38.544' AS DATETIME) AS UsingCast,
CONVERT(DATETIME, '2010-07-25T13:50:38.544', 126) AS UsingConvertFrom_ISO8601
FROM dbo.DimCustomer;Result Set:
UnconvertedText UsingCast UsingConvertFrom_ISO8601
----------------------- ----------------------- ------------------------
2010-07-25T13:50:38.544 2010-07-25 13:50:38.543 2010-07-25 13:50:38.543See Also
Next Steps
- Explore more about Transact-SQL Functions to enhance your SQL Server programming skills.
- Deepen your understanding of SQL Server Data Types for efficient database design and management.
