CAST and CONVERT (Transact-SQL) - SQL Server (2023)

  • Article
  • 27 minutes to read

Applies to: CAST and CONVERT (Transact-SQL) - SQL Server (1) SQL Server CAST and CONVERT (Transact-SQL) - SQL Server (2) Azure SQL Database CAST and CONVERT (Transact-SQL) - SQL Server (3) Azure SQL Managed Instance CAST and CONVERT (Transact-SQL) - SQL Server (4) Azure Synapse Analytics CAST and CONVERT (Transact-SQL) - SQL Server (5) Analytics Platform System (PDW)

These functions convert an expression of one data type to another.

Syntax

CAST syntax:

CAST ( expression AS data_type [ ( length ) ] )

CONVERT syntax:

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

CAST and CONVERT (Transact-SQL) - SQL Server (6) Transact-SQL syntax conventions

Note

To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation.

Arguments

expression

Any valid expression.

data_type

The target data type. This includes xml, bigint, and sql_variant. Alias data types can't be used.

length

An optional integer that specifies the length of the target data type, for data types that allow a user specified length. The default value is 30.

style

An integer expression that specifies how the CONVERT function will translate expression. For a style value of NULL, NULL is returned. data_type determines the range.

Return types

Returns expression, translated to data_type.

Date and time styles

For a date or time data type expression, style can have one of the values shown in the following table. Other values are processed as 0. Beginning with SQL Server 2012 (11.x), the only styles supported, when converting from date and time types to datetimeoffset, are 0 or 1. All other conversion styles return error 9809.

Note

SQL Server supports the date format, in Arabic style, with the Kuwaiti algorithm.

Without century (yy) 1With century (yyyy)StandardInput/output 3
-0 or 100 1, 2Default for datetime and smalldatetimemon dd yyyy hh:miAM (or PM)
1101U.S.1 = mm/dd/yy
101 = mm/dd/yyyy
2102ANSI2 = yy.mm.dd
102 = yyyy.mm.dd
3103British/French3 = dd/mm/yy
103 = dd/mm/yyyy
4104German4 = dd.mm.yy
104 = dd.mm.yyyy
5105Italian5 = dd-mm-yy
105 = dd-mm-yyyy
6106 1-6 = dd mon yy
106 = dd mon yyyy
7107 1-7 = Mon dd, yy
107 = Mon dd, yyyy
8 or 24108-hh:mi:ss
-9 or 109 1, 2Default + millisecondsmon dd yyyy hh:mi:ss:mmmAM (or PM)
10110USA10 = mm-dd-yy
110 = mm-dd-yyyy
11111JAPAN11 = yy/mm/dd
111 = yyyy/mm/dd
12112ISO12 = yymmdd
112 = yyyymmdd
-13 or 113 1, 2Europe default + millisecondsdd mon yyyy hh:mi:ss:mmm (24-hour)
14114-hh:mi:ss:mmm (24-hour)
-20 or 120 2ODBC canonicalyyyy-mm-dd hh:mi:ss (24-hour)
-21 or 25 or 121 2ODBC canonical (with milliseconds) default for time, date, datetime2, and datetimeoffsetyyyy-mm-dd hh:mi:ss.mmm (24-hour)
22-U.S.mm/dd/yy hh:mi:ss AM (or PM)
-23ISO8601yyyy-mm-dd
-126 4ISO8601yyyy-mm-ddThh:mi:ss.mmm (no spaces) 6
-127 8, 9ISO8601 with time zone Zyyyy-MM-ddThh:mm:ss.fffZ (no spaces) 6
-130 1, 2Hijri 5dd mon yyyy hh:mi:ss:mmmAM 7
-131 2Hijri 5dd/mm/yyyy hh:mi:ss:mmmAM

1 These style values return nondeterministic results. Includes all (yy) (without century) styles and a subset of (yyyy) (with century) styles.

2 The default values (0 or 100, 9 or 109, 13 or 113, 20 or 120, 23, and 21 or 25 or 121) always return the century (yyyy).

Important

By default, SQL Server interprets two-digit years based on a cutoff year of 2049. That means that SQL Server interprets the two-digit year 49 as 2049 and the two-digit year 50 as 1950. Many client applications, including those based on Automation objects, use a cutoff year of 2030. SQL Server provides the two digit year cutoff configuration option to change the cutoff year used by SQL Server. This allows for the consistent treatment of dates. We recommend specifying four-digit years.

3 Input when you convert to datetime; output when you convert to character data.

4 Designed for XML use. For conversion from datetime or smalldatetime to character data, see the previous table for the output format.

(Video) CAST() and CONVERT() in SQL Server

5 Hijri is a calendar system with several variations. SQL Server uses the Kuwaiti algorithm.

6 For a milliseconds (mmm) value of 0, the millisecond decimal fraction value won't display. For example, the value 2022-11-07T18:26:20.000 displays as 2022-11-07T18:26:20.

7 In this style, mon represents a multi-token Hijri unicode representation of the full month name. This value doesn't render correctly on a default US installation of SSMS.

8 Only supported when casting from character data to datetime or smalldatetime. When casting character data representing only date or only time components to the datetime or smalldatetime data types, the unspecified time component is set to 00:00:00.000, and the unspecified date component is set to 1900-01-01.

9 Use the optional time zone indicator Z to make it easier to map XML datetime values that have time zone information to SQL Server datetime values that have no time zone. Z indicates time zone at UTC-0. The HH:MM offset, in the + or - direction, indicates other time zones. For example: 2022-12-12T23:45:12-08:00.

When converting smalldatetime to character data, the styles that include seconds or milliseconds show zeros in these positions. When converting from datetime or smalldatetime values, use an appropriate char or varchar data type length to truncate unwanted date parts.

When converting character data to datetimeoffset, using a style that includes a time, a time zone offset is appended to the result.

float and real styles

For a float or real expression, style can have one of the values shown in the following table. Other values are processed as 0.

ValueOutput
0 (default)A maximum of 6 digits. Use in scientific notation, when appropriate.
1Always 8 digits. Always use in scientific notation.
2Always 16 digits. Always use in scientific notation.
3Always 17 digits. Use for lossless conversion. With this style, every distinct float or real value is guaranteed to convert to a distinct character string.

Applies to: SQL Server 2016 (13.x) and later versions, and Azure SQL Database.

126, 128, 129Included for legacy reasons. Don't use these values for new development.

money and smallmoney styles

For a money or smallmoney expression, style can have one of the values shown in the following table. Other values are processed as 0.

ValueOutput
0 (default)No commas every three digits to the left of the decimal point, and two digits to the right of the decimal point

Example: 4235.98.

1Commas every three digits to the left of the decimal point, and two digits to the right of the decimal point

Example: 3,510.92.

2No commas every three digits to the left of the decimal point, and four digits to the right of the decimal point

Example: 4235.9819.

126Equivalent to style 2, when converting to char(n) or varchar(n)

xml styles

For an xml expression, style can have one of the values shown in the following table. Other values are processed as 0.

ValueOutput
0 (default)Use default parsing behavior that discards insignificant white space, and doesn't allow for an internal DTD subset.

Note: When converting to the xml data type, SQL Server insignificant white space is handled differently than in XML 1.0. For more information, see Create Instances of XML Data.

1Preserve insignificant white space. This style setting sets the default xml:space handling to match the behavior of xml:space="preserve".
2Enable limited internal DTD subset processing.

If enabled, the server can use the following information that is provided in an internal DTD subset, to perform nonvalidating parse operations.

- Defaults for attributes are applied
- Internal entity references are resolved and expanded
- The DTD content model is checked for syntactical correctness

The parser ignores external DTD subsets. Also, it doesn't evaluate the XML declaration to see whether the standalone attribute has a yes or no value. Instead, it parses the XML instance as a stand-alone document.

3Preserve insignificant white space, and enable limited internal DTD subset processing.

Binary styles

For a binary(n), char(n), varbinary(n), or varchar(n) expression, style can have one of the values shown in the following table. Style values not listed in the table will return an error.

ValueOutput
0 (default)Translates ASCII characters to binary bytes, or binary bytes to ASCII characters. Each character or byte is converted 1:1.

For a binary data_type, the characters 0x are added to the left of the result.

1, 2For a binary data_type, the expression must be a character expression. The expression must have an even number of hexadecimal digits (0, 1, 2, 3, 4, 5, 6, 7, 8, 9, A, B, C, D, E, F, a, b, c, d, e, f). If the style is set to 1, the expression must have 0x as the first two characters. If the expression contains an odd number of characters, or if any of the characters is invalid, an error is raised.

If the length of the converted expression exceeds the length of the data_type, the result is right truncated.

Fixed length data_types larger than the converted result has zeros added to the right of the result.

A data_type of type character requires a binary expression. Each binary character is converted into two hexadecimal characters. Suppose the length of the converted expression exceeds the length of the data_type. In that case, it's truncated.

For a fixed size character type data_type, if the length of the converted result is less than its length of the data_type, spaces are added to the right of the converted expression to maintain an even number of hexadecimal digits.

The characters 0x aren't added to the left of the converted result for style 2.

Implicit conversions

Implicit conversions don't require specification of either the CAST function or the CONVERT function. Explicit conversions require specification of the CAST function or the CONVERT function. The following illustration shows all explicit and implicit data type conversions allowed for SQL Server system-supplied data types. These include bigint, and sql_variant, and xml. There is no implicit conversion on assignment from the sql_variant data type, but there is implicit conversion to sql_variant.

Tip

The Microsoft Download Center has this chart available for download as a PNG file.

CAST and CONVERT (Transact-SQL) - SQL Server (7)

The above chart illustrates all the explicit and implicit conversions that are allowed in SQL Server, but the resulting data type of the conversion depends on the operation being performed:

  • For explicit conversions, the statement itself determines the resulting data type.
  • For implicit conversions, assignment statements such as setting the value of a variable or inserting a value into a column will result in the data type that was defined by the variable declaration or column definition.
  • For comparison operators or other expressions, the resulting data type will depend on the rules of data type precedence.

Tip

A practical example on the effects of data type precedence in conversions can be seen later in this section.

When you convert between datetimeoffset and the character types char, nchar, nvarchar, and varchar, the converted time zone offset part should always have double digits for both HH and MM. For example, -08:00.

Because Unicode data always uses an even number of bytes, use caution when you convert binary or varbinary to or from Unicode supported data types. For example, the following conversion doesn't return a hexadecimal value of 41. It returns a hexadecimal value of 4100:

SELECT CAST(CAST(0x41 AS nvarchar) AS varbinary);

For more information, see Collation and Unicode Support.

Large-value data types

Large-value data types have the same implicit and explicit conversion behavior as their smaller counterparts - specifically, the nvarchar, varbinary, and varchar data types. However, consider the following guidelines:

  • Conversion from image to varbinary(max), and vice-versa, operates as an implicit conversion, as do conversions between text and varchar(max), and ntext and nvarchar(max).
  • Conversion from large-value data types, such as varchar(max), to a smaller counterpart data type, such as varchar, is an implicit conversion, but truncation occurs if the size of the large value exceeds the specified length of the smaller data type.
  • Conversion from nvarchar, varbinary, or varchar to their corresponding large-value data types happens implicitly.
  • Conversion from the sql_variant data type to the large-value data types is an explicit conversion.
  • Large-value data types can't be converted to the sql_variant data type.

For more information about conversion from the xml data type, see Create Instances of XML Data.

xml data type

When you explicitly or implicitly cast the xml data type to a string or binary data type, the content of the xml data type is serialized based on a defined set of rules. For information about these rules, see Define the Serialization of XML Data. For information about conversion from other data types to the xml data type, see Create Instances of XML Data.

(Video) SQL SERVER Tutorial - T-SQL: Using CAST & CONVERT - Using STR

text and image data types

The text and image data types don't support automatic data type conversion. You can explicitly convert text data to character data, and image data to binary or varbinary, but the maximum length is 8000 bytes. If you try an incorrect conversion, for example trying to convert a character expression that includes letters to an int, SQL Server returns an error message.

Output collation

When the CAST or CONVERT functions output a character string, and they receive a character string input, the output has the same collation and collation label as the input. If the input isn't a character string, the output has the default collation of the database, and a collation label of coercible-default. For more information, see Collation Precedence (Transact-SQL).

To assign a different collation to the output, apply the COLLATE clause to the result expression of the CAST or CONVERT function. For example:

SELECT CAST('abc' AS varchar(5)) COLLATE French_CS_AS;

Truncating and rounding results

When converting character or binary expressions (binary, char, nchar, nvarchar, varbinary, or varchar) to an expression of a different data type, the conversion operation could truncate the output data, only partially display the output data, or return an error. These cases will occur if the result is too short to display. Conversions to binary, char, nchar, nvarchar, varbinary, or varchar are truncated, except for the conversions shown in the following table.

From data typeTo data typeResult
int, smallint, or tinyintchar
varchar
Too short to display
nchar
nvarchar
Error 1
money, smallmoney, numeric, decimal, float, or realchar
varchar
Error 1
nchar
nvarchar
Error 1

1 Error returned because result length is too short to display.

SQL Server guarantees that only roundtrip conversions, in other words conversions that convert a data type from its original data type and back again, yield the same values from version to version. The following example shows such a roundtrip conversion:

DECLARE @myval DECIMAL(5, 2);SET @myval = 193.57;SELECT CAST(CAST(@myval AS VARBINARY(20)) AS DECIMAL(10, 5));-- Or, using CONVERTSELECT CONVERT(DECIMAL(10, 5), CONVERT(VARBINARY(20), @myval));GO

Warning

Don't construct binary values, and then convert them to a data type of the numeric data type category. SQL Server does not guarantee that the result of a decimal or numeric data type conversion, to binary, will be the same between versions of SQL Server.

The following example shows a resulting expression that is too small to display.

USE AdventureWorks2019;GOSELECT p.FirstName, p.LastName, SUBSTRING(p.Title, 1, 25) AS Title, CAST(e.SickLeaveHours AS CHAR(1)) AS [Sick Leave]FROM HumanResources.Employee eINNER JOIN Person.Person p ON e.BusinessEntityID = p.BusinessEntityIDWHERE NOT e.BusinessEntityID > 5;GO

Here is the result 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)

When you convert data types that differ in decimal places, SQL Server will sometimes return a truncated result value, and at other times it will return a rounded value. This table shows the behavior.

FromToBehavior
numericnumericRound
numericintTruncate
numericmoneyRound
moneyintRound
moneynumericRound
floatintTruncate
floatnumericRound 1
floatdatetimeRound
datetimeintRound

1 Conversion of float values that use scientific notation to decimal or numeric is restricted to values of precision 17 digits only. Any value with precision higher than 17 rounds to zero.

For example, the values 10.6496 and -10.6496 may be truncated or rounded during conversion to int or numeric types:

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 of the query are shown in the following table:

trunc1trunc2round1round2
10-1011-11

When converting data types where the target data type has fewer decimal places than the source data type, the value is rounded. For example, this conversion returns $10.3497:

SELECT CAST(10.3496847 AS money);

SQL Server returns an error message when converting nonnumeric char, nchar, nvarchar, or varchar data to decimal, float, int, numeric. SQL Server also returns an error when an empty string (" ") is converted to numeric or decimal.

Certain datetime conversions are nondeterministic

The styles for which the string-to-datetime conversion is nondeterministic are as follows:

  • All styles below 100 1
  • 106
  • 107
  • 109
  • 113
  • 130

1 With the exception of styles 20 and 21

For more information, see Nondeterministic conversion of literal date strings into DATE values.

Supplementary characters (surrogate pairs)

Starting with SQL Server 2012 (11.x), when using supplementary character (SC) collations, a CAST operation from nchar or nvarchar to an nchar or nvarchar type of smaller length won't truncate inside a surrogate pair. Instead, the operation truncates before the supplementary character. For example, the following code fragment leaves @x holding just 'ab'. There isn't enough space to hold the supplementary character.

DECLARE @x NVARCHAR(10) = 'ab' + NCHAR(0x10000);SELECT CAST(@x AS NVARCHAR(3));

When using SC collations, the behavior of CONVERT, is analogous to that of CAST. For more information, see Collation and Unicode Support - Supplementary Characters.

Compatibility support

In earlier versions of SQL Server, the default style for CAST and CONVERT operations on time and datetime2 data types is 121, except when either type is used in a computed column expression. For computed columns, the default style is 0. This behavior impacts computed columns when they are created, used in queries involving auto-parameterization, or used in constraint definitions.

Under compatibility level 110 and higher, the CAST and CONVERT operations on the time and datetime2 data types always have 121 as the default style. If a query relies on the old behavior, use a compatibility level less than 110, or explicitly specify the 0 style in the affected query.

Compatibility level valueDefault style for CAST and CONVERT 1Default style for computed column
< 1101210
> = 110121121

1 Except for computed columns

Upgrading the database to compatibility level 110 and higher won't change user data that has been stored to disk. You must manually correct this data as appropriate. For example, if you used SELECT INTO to create a table from a source containing a computed column expression described above, the data (using style 0) would be stored rather than the computed column definition itself. You must manually update this data to match style 121.

(Video) MS SQL tutorial on CONVERT and CAST functions

Examples

A. Use both CAST and CONVERT

These examples retrieve the name of the product, for those products that have a 3 as the first digit of list price, and converts their ListPrice values to int.

Use CAST:

USE AdventureWorks2019;GOSELECT SUBSTRING(Name, 1, 30) AS ProductName, ListPriceFROM Production.ProductWHERE CAST(ListPrice AS INT) LIKE '33%';GO

Use CONVERT:

USE AdventureWorks2019;GOSELECT SUBSTRING(Name, 1, 30) AS ProductName, ListPriceFROM Production.ProductWHERE CONVERT(INT, ListPrice) LIKE '33%';GO

Here is the result set. The sample result set is the same for both CAST and CONVERT.

ProductName ListPrice------------------------------ ---------------------LL Road Frame - Black, 58 337.22LL Road Frame - Black, 60 337.22LL Road Frame - Black, 62 337.22LL Road Frame - Red, 44 337.22LL Road Frame - Red, 48 337.22LL Road Frame - Red, 52 337.22LL Road Frame - Red, 58 337.22LL Road Frame - Red, 60 337.22LL Road Frame - Red, 62 337.22LL Road Frame - Black, 44 337.22LL Road Frame - Black, 48 337.22LL Road Frame - Black, 52 337.22Mountain-100 Black, 38 3374.99Mountain-100 Black, 42 3374.99Mountain-100 Black, 44 3374.99Mountain-100 Black, 48 3374.99HL Road Front Wheel 330.06LL Touring Frame - Yellow, 62 333.42LL Touring Frame - Blue, 50 333.42LL Touring Frame - Blue, 54 333.42LL Touring Frame - Blue, 58 333.42LL Touring Frame - Blue, 62 333.42LL Touring Frame - Yellow, 44 333.42LL Touring Frame - Yellow, 50 333.42LL Touring Frame - Yellow, 54 333.42LL Touring Frame - Yellow, 58 333.42LL Touring Frame - Blue, 44 333.42HL Road Tire 32.60(28 rows affected)

B. Use CAST with arithmetic operators

This example calculates a single column computation (Computed) by dividing the total year-to-date sales (SalesYTD) by the commission percentage (CommissionPCT). This value is rounded to the nearest whole number and is then CAST to an int data type.

USE AdventureWorks2019;GOSELECT CAST(ROUND(SalesYTD / CommissionPCT, 0) AS INT) AS ComputedFROM Sales.SalesPersonWHERE CommissionPCT != 0;GO

Here is the result set.

Computed------379753754346698349257144242176493899281101272030187254921262375029894820225078411923924689010166422012451133697688107(14 row(s) affected)

C. Use CAST to concatenate

This example concatenates noncharacter expressions by using CAST. It uses the AdventureWorksDW2019 database.

SELECT 'The list price is ' + CAST(ListPrice AS VARCHAR(12)) AS ListPriceFROM dbo.DimProductWHERE ListPrice BETWEEN 350.00 AND 400.00;

Here is the result set.

ListPrice------------------------The list price is 357.06The list price is 364.09The list price is 364.09The list price is 364.09The list price is 364.09

D. Use CAST to produce more readable text

This example uses CAST in the SELECT list, to convert the Name column to a char(10) column. It uses the AdventureWorksDW2019 database.

SELECT DISTINCT CAST(EnglishProductName AS CHAR(10)) AS Name, ListPriceFROM dbo.DimProductWHERE EnglishProductName LIKE 'Long-Sleeve Logo Jersey, M';GO

Here is the result set.

Name ListPrice---------- ---------Long-Sleev 31.2437Long-Sleev 32.4935Long-Sleev 49.99

E. Use CAST with the LIKE clause

This example converts the money column SalesYTD values to data type int, and then to data type char(20), so that the LIKE clause can use it.

USE AdventureWorks2019;GOSELECT p.FirstName, p.LastName, s.SalesYTD, s.BusinessEntityIDFROM Person.Person AS pINNER JOIN Sales.SalesPerson AS s ON p.BusinessEntityID = s.BusinessEntityIDWHERE CAST(CAST(s.SalesYTD AS INT) AS CHAR(20)) LIKE '2%';GO

Here is the result set.

FirstName LastName SalesYTD BusinessEntityID---------------- ------------------- ---------------- -------------Tsvi Reiter 2811012.7151 279Syed Abbas 219088.8836 288Rachel Valdez 2241204.0424 289(3 row(s) affected)

F. Use CONVERT or CAST with typed XML

These examples show use of CONVERT to convert data to typed XML, by using the XML Data Type and Columns (SQL Server).

This example converts a string with white space, text and markup into typed XML, and removes all insignificant white space (boundary white space between nodes):

SELECT CONVERT(XML, '<root><child/></root>')

This example converts a similar string with white space, text and markup into typed XML and preserves insignificant white space (boundary white space between nodes):

SELECT CONVERT(XML, '<root> <child/> </root>', 1)

This example casts a string with white space, text, and markup into typed XML:

SELECT CAST('<Name><FName>Carol</FName><LName>Elliot</LName></Name>' AS XML)

See Create Instances of XML Data for more examples.

G. Use CAST and CONVERT with datetime data

Starting with GETDATE() values, this example displays the current date and time, uses CAST to change the current date and time to a character data type, and then uses CONVERT to display the date and time in the ISO 8601 format.

SELECT GETDATE() AS UnconvertedDateTime, CAST(GETDATE() AS NVARCHAR(30)) AS UsingCast, CONVERT(NVARCHAR(30), GETDATE(), 126) AS UsingConvertTo_ISO8601;GO

Here is the result set.

UnconvertedDateTime UsingCast UsingConvertTo_ISO8601----------------------- ---------------------- ------------------------------2022-04-18 09:58:04.570 Apr 18 2022 9:58AM 2022-04-18T09:58:04.570(1 row(s) affected)

This example is approximately the opposite of the previous example. This example displays a date and time as character data, uses CAST to change the character data to the datetime data type, and then uses CONVERT to change the character data to the datetime data type.

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;GO

Here is the result 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. Use CONVERT with binary and character data

These examples show the results of binary and character data conversion, using different styles.

--Convert the binary value 0x4E616d65 to a character value.SELECT CONVERT(CHAR(8), 0x4E616d65, 0) AS [Style 0, binary to character];

Here is the result set.

Style 0, binary to character----------------------------Name(1 row(s) affected)

This example shows that Style 1 can force result truncation. The characters 0x in the result set force the truncation.

SELECT CONVERT(CHAR(8), 0x4E616d65, 1) AS [Style 1, binary to character];

Here is the result set.

Style 1, binary to character------------------------------0x4E616D(1 row(s) affected)

This example shows that Style 2 doesn't truncate the result, because the result doesn't include the characters 0x.

(Video) Convert numbers and dates into strings in SQL Server using the CAST and TRY_CAST functions

SELECT CONVERT(CHAR(8), 0x4E616d65, 2) AS [Style 2, binary to character];

Here is the result set.

Style 2, binary to character------------------------------4E616D65(1 row(s) affected)

Convert the character value 'Name' to a binary value.

SELECT CONVERT(BINARY(8), 'Name', 0) AS [Style 0, character to binary];

Here is the result set.

Style 0, character to binary----------------------------0x4E616D6500000000(1 row(s) affected)
SELECT CONVERT(BINARY(4), '0x4E616D65', 1) AS [Style 1, character to binary];

Here is the result set.

Style 1, character to binary----------------------------0x4E616D65(1 row(s) affected)
SELECT CONVERT(BINARY(4), '4E616D65', 2) AS [Style 2, character to binary];

Here is the result set.

Style 2, character to binary----------------------------------0x4E616D65(1 row(s) affected)

I. Convert date and time data types

This example shows the conversion of 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 the values are within a compatible range when considering a conversion from date to datetime or datetime2. The minimum year value for datetime is 1753, while the minimum year value is 0001 for date and datetime2.

DECLARE @d1 DATE, @dt1 DATETIME , @dt2 DATETIME2SET @d1 = '1492-08-03'--This is okay; Minimum YYYY for DATE is 0001SET @dt2 = CAST(@d1 AS DATETIME2)--This is okay; Minimum YYYY for DATETIME2 IS 0001SET @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 1753

J. Use CONVERT with datetime data in different formats

Starting with GETDATE() values, this example uses CONVERT to display of all the date and time styles in section Date and Time styles of this article.

Format #Example querySample result
0SELECT CONVERT(NVARCHAR, GETDATE(), 0)Aug 23 2019 1:39PM
1SELECT CONVERT(NVARCHAR, GETDATE(), 1)08/23/19
2SELECT CONVERT(NVARCHAR, GETDATE(), 2)19.08.23
3SELECT CONVERT(NVARCHAR, GETDATE(), 3)23/08/19
4SELECT CONVERT(NVARCHAR, GETDATE(), 4)23.08.19
5SELECT CONVERT(NVARCHAR, GETDATE(), 5)23-08-19
6SELECT CONVERT(NVARCHAR, GETDATE(), 6)23 Aug 19
7SELECT CONVERT(NVARCHAR, GETDATE(), 7)Aug 23, 19
8 or 24 or 108SELECT CONVERT(NVARCHAR, GETDATE(), 8)13:39:17
9 or 109SELECT CONVERT(NVARCHAR, GETDATE(), 9)Aug 23 2019 1:39:17:090PM
10SELECT CONVERT(NVARCHAR, GETDATE(), 10)08-23-19
11SELECT CONVERT(NVARCHAR, GETDATE(), 11)19/08/23
12SELECT CONVERT(NVARCHAR, GETDATE(), 12)190823
13 or 113SELECT CONVERT(NVARCHAR, GETDATE(), 13)23 Aug 2019 13:39:17:090
14 or 114SELECT CONVERT(NVARCHAR, GETDATE(), 14)13:39:17:090
20 or 120SELECT CONVERT(NVARCHAR, GETDATE(), 20)2019-08-23 13:39:17
21 or 25 or 121SELECT CONVERT(NVARCHAR, GETDATE(), 21)2019-08-23 13:39:17.090
22SELECT CONVERT(NVARCHAR, GETDATE(), 22)08/23/19 1:39:17 PM
23SELECT CONVERT(NVARCHAR, GETDATE(), 23)2019-08-23
101SELECT CONVERT(NVARCHAR, GETDATE(), 101)08/23/2019
102SELECT CONVERT(NVARCHAR, GETDATE(), 102)2019.08.23
103SELECT CONVERT(NVARCHAR, GETDATE(), 103)23/08/2019
104SELECT CONVERT(NVARCHAR, GETDATE(), 104)23.08.2019
105SELECT CONVERT(NVARCHAR, GETDATE(), 105)23-08-2019
106SELECT CONVERT(NVARCHAR, GETDATE(), 106)23 Aug 2019
107SELECT CONVERT(NVARCHAR, GETDATE(), 107)Aug 23, 2019
110SELECT CONVERT(NVARCHAR, GETDATE(), 110)08-23-2019
111SELECT CONVERT(NVARCHAR, GETDATE(), 111)2019/08/23
112SELECT CONVERT(NVARCHAR, GETDATE(), 112)20190823
113SELECT CONVERT(NVARCHAR, GETDATE(), 113)23 Aug 2019 13:39:17.090
120SELECT CONVERT(NVARCHAR, GETDATE(), 120)2019-08-23 13:39:17
121SELECT CONVERT(NVARCHAR, GETDATE(), 121)2019-08-23 13:39:17.090
126SELECT CONVERT(NVARCHAR, GETDATE(), 126)2019-08-23T13:39:17.090
127SELECT CONVERT(NVARCHAR, GETDATE(), 127)2019-08-23T13:39:17.090
130SELECT CONVERT(NVARCHAR, GETDATE(), 130)22 ذو الحجة 1440 1:39:17.090P
131SELECT CONVERT(NVARCHAR, GETDATE(), 131)22/12/1440 1:39:17.090PM

K. Effects of data type precedence in allowed conversions

The following example defines a variable of type varchar(10), assigns an integer value to the variable, then selects a concatenation of the variable with a string.

DECLARE @string VARCHAR(10);SET @string = 1;SELECT @string + ' is a string.' AS Result

Here is the result set.

Result-----------------------1 is a string.

The int value of 1 was converted to a varchar.

This example shows a similar query, using an int variable instead:

DECLARE @notastring INT;SET @notastring = '1';SELECT @notastring + ' is not a string.' AS Result

In this case, the SELECT statement will throw the following error:

Msg 245, Level 16, State 1, Line 3Conversion failed when converting the varchar value ' is not a string.' to data type int.

In order to evaluate the expression @notastring + ' is not a string.', SQL Server needs to follow the rules of data type precedence to complete the implicit conversion before the result of the expression can be calculated. Because int has a higher precedence than varchar, SQL Server attempts to convert the string to an integer and fails because this string can't be converted to an integer.

If we provide a string that can be converted, the statement will succeed, as seen in the following example:

DECLARE @notastring INT;SET @notastring = '1';SELECT @notastring + '1'

In this case, the string '1' can be converted to the integer value 1, so this SELECT statement will return the value 2. When the data types provided are integers, the + operator becomes addition mathematical operator, rather than a string concatenation.

Examples: Azure Synapse Analytics and Analytics Platform System (PDW)

L. Use CAST and CONVERT

This example retrieves the name of the product for those products that have a 3 in the first digit of their list price, and converts the ListPrice of these products to int. It uses the AdventureWorksDW2019 database.

SELECT EnglishProductName AS ProductName, ListPriceFROM dbo.DimProductWHERE CAST(ListPrice AS int) LIKE '3%';

This example shows the same query, using CONVERT instead of CAST. It uses the AdventureWorksDW2019 database.

SELECT EnglishProductName AS ProductName, ListPriceFROM dbo.DimProductWHERE CONVERT(INT, ListPrice) LIKE '3%';

M. Use CAST with arithmetic operators

This example calculates a single column value by dividing the product unit price (UnitPrice) by the discount percentage (UnitPriceDiscountPct). This result is then rounded to the nearest whole number, and finally converted to an int data type. This example uses the AdventureWorksDW2019 database.

SELECT ProductKey, UnitPrice,UnitPriceDiscountPct, CAST(ROUND (UnitPrice*UnitPriceDiscountPct,0) AS int) AS DiscountPriceFROM dbo.FactResellerSalesWHERE SalesOrderNumber = 'SO47355' AND UnitPriceDiscountPct > .02;

Here is the result set.

ProductKey UnitPrice UnitPriceDiscountPct DiscountPrice---------- --------- -------------------- -------------323 430.6445 0.05 22213 18.5043 0.05 1456 37.4950 0.10 4456 37.4950 0.10 4216 18.5043 0.05 1

N. Use CAST with the LIKE clause

This example converts the money column ListPrice to an int type, and then to a char(20) type, so that the LIKE clause can use it. This example uses the AdventureWorksDW2019 database.

SELECT EnglishProductName AS Name, ListPriceFROM dbo.DimProductWHERE CAST(CAST(ListPrice AS INT) AS CHAR(20)) LIKE '2%';

O. Use CAST and CONVERT with datetime data

This example displays the current date and time, uses CAST to change the current date and time to a character data type, and finally uses CONVERT display the date and time in the ISO 8601 format. This example uses the AdventureWorksDW2019 database.

SELECT TOP(1) SYSDATETIME() AS UnconvertedDateTime, CAST(SYSDATETIME() AS NVARCHAR(30)) AS UsingCast, CONVERT(NVARCHAR(30), SYSDATETIME(), 126) AS UsingConvertTo_ISO8601FROM dbo.DimCustomer;

Here is the result set.

UnconvertedDateTime UsingCast UsingConvertTo_ISO8601--------------------- --------------------------- ---------------------------07/20/2010 1:44:31 PM 2010-07-20 13:44:31.5879025 2010-07-20T13:44:31.5879025

This example is the rough opposite of the previous example. This example displays a date and time as character data, uses CAST to change the character data to the datetime data type, and then uses CONVERT to change the character data to the datetime data type. This example uses the AdventureWorksDW2019 database.

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_ISO8601FROM dbo.DimCustomer;

Here is the result set.

UnconvertedText UsingCast UsingConvertFrom_ISO8601----------------------- ----------------------- ------------------------2010-07-25T13:50:38.544 07/25/2010 1:50:38 PM 07/25/2010 1:50:38 PM

See also

  • FORMAT (Transact-SQL)
  • STR (Transact-SQL)
  • SELECT (Transact-SQL)
  • System Functions (Transact-SQL)
  • Collation and Unicode Support

Next steps

  • Data type precedence (Transact-SQL)
  • Data type conversion (Database Engine)
  • Write International Transact-SQL Statements

FAQs

What is the difference between CAST and convert in TSQL? ›

CAST is part of the ANSI-SQL specification; whereas, CONVERT is not. In fact, CONVERT is SQL implementation-specific. CONVERT differences lie in that it accepts an optional style parameter that is used for formatting.

What is CAST () and convert () functions in SQL Server? ›

The SQL CAST function is mainly used to convert the expression from one data type to another data type. If the SQL Server CAST function is unable to convert a declaration to the desired data type, this function returns an error. We use the CAST function to convert numeric data into character or string data.

What is the difference between CAST and convert? ›

1. In type casting, a data type is converted into another data type by a programmer using casting operator. Whereas in type conversion, a data type is converted into another data type by a compiler.

Which is faster CAST or convert? ›

Summary. Casting and converting are ways in which we can change a value from one type to another; casting is faster but more prone to errors, while conversion is more computationally expensive but also more forgiving.

Which is better CONVERT or CAST? ›

CAST is also less powerful and less flexible than CONVERT. On the other hand, CONVERT allows more flexibility and is the preferred function to use for data, time values, traditional numbers, and money signifiers. CONVERT is also useful in formatting the data's format.

Which is better parse or CONVERT? ›

Convert. ToInt32 allows null value, it doesn't throw any errors Int. parse does not allow null value, and it throws an ArgumentNullException error.

What is the purpose of using CONVERT function in SQL Server? ›

In SQL Server (Transact-SQL), the CONVERT function converts an expression from one datatype to another datatype. If the conversion fails, the function will return an error. Otherwise, it will return the converted value. TIP: Use the TRY_CONVERT function to return a NULL (instead of an error) if the conversion fails.

What is the use of CONVERT function in SQL? ›

The CONVERT() function converts a value (of any type) into a specified datatype.

Is CONVERT () a function? ›

Converts a number from one measurement system to another. For example, CONVERT can translate a table of distances in miles to a table of distances in kilometers.

Does casting permanently change data type? ›

Casting does not permanently alter the data type of a value; it expresses the value in a more convenient form. Casting user-defined data types into built-in types allows client programs to manipulate data types without knowledge of their internal structure.

What is the difference between Try_cast and Try_convert in SQL Server? ›

TRY_CAST & TRY_CONVERT follow the same syntax as CAST and CONVERT but with one key difference: TRY_CAST and TRY_CONVERT return NULL if the conversion fails.

What is the difference between convert and try convert? ›

If the cast fails, the TRY_CONVERT() function returns NULL while the CONVERT() function raises an error. This is the main difference between the two functions. You can use the NULL handling functions or expressions such as ISNULL() and COALESCE to handle the result of the TRY_CONVERT() function in case the cast fails.

What are the 4 types of conversion? ›

Direct Conversion 2. Parallel conversion 3. Modular Conversion 4. Phase-In Conversion.

What are the three types of conversion? ›

The possible types of conversion are standard conversion, no conversion, and user-defined nonstandard conversion.

What are the two types of conversion? ›

There are two types of conversion: implicit and explicit. The term for implicit type conversion is coercion. Explicit type conversion in some specific way is known as casting. Explicit type conversion can also be achieved with separately defined conversion routines such as an overloaded object constructor.

What is the difference between try parse and try convert in SQL? ›

The difference between TRY_CONVERT and TRY_PARSE is simple. TRY_PARSE can only convert string data type to numeric or date data types while TRY_CONVERT can be used for any general type conversion.

Which is better traffic or conversion? ›

The conversion rate is, no doubt, the most vital metric in digital marketing, more so than website traffic. They are both essential, but having many visitors doesn't do you any good if they don't buy your products or services.

How to convert data from SQL to C data types? ›

  1. Setup. SQL Server. Analysis Services (SSAS) Integration Services (SSIS) Reporting Services (SSRS) ...
  2. Upgrade. SQL Server. Analysis Services (SSAS) Integration Services (SSIS) Reporting Services (SSRS)
  3. Migrate. Azure Database Migration Service. Database Migration Assistant. SQL Server Migration Assistant.
Nov 18, 2022

Is Parse still used? ›

Parse is a widely used open-source framework for the development of application backends.

Which parser is faster? ›

SAX is faster because DOM parsers often use a SAX parser to parse a document internally, then do the extra work of creating and manipulating objects to represent each and every node, even if the application doesn't care about them.

What is convert used for? ›

convert implies a change fitting something for a new or different use or function.

What is the difference between CAST and convert in SQL w3schools? ›

CAST is part of the ANSI-SQL specification; whereas, CONVERT is not. In fact, CONVERT is SQL implementation specific. CONVERT differences lie in that that accepts an optional style parameter which is used for formatting.

How do I write a convert function in SQL Server? ›

SQL CONVERT function: Syntax

An integer that specifies the length of the destination data type. A valid value to be converted. An integer expression that instructs how the function will convert the expression. The specified data type defines the range of values for the style argument.

How to change date format from dd mm yyyy to yyyy mm dd in SQL Server? ›

How to get different date formats in SQL Server
  1. Use the SELECT statement with CONVERT function and date format option for the date values needed.
  2. To get YYYY-MM-DD use this T-SQL syntax SELECT CONVERT(varchar, getdate(), 23)
  3. To get MM/DD/YY use this T-SQL syntax SELECT CONVERT(varchar, getdate(), 1)
Dec 8, 2022

What is the process of convert? ›

The conversion process searches for the first rule that matches the incoming data, and then applies the action associated with that rule. The output of that action can then be further processed by other rules, depending on its type-out value and whether other rules exist that are triggered by that value.

How do I CAST a string to a date? ›

We can convert String to Date in java using parse() method of DateFormat and SimpleDateFormat classes. To learn this concept well, you should visit DateFormat and SimpleDateFormat classes.

How to convert SQL date to string? ›

In order to convert a DateTime to a string, we can use CONVERT() and CAST() function. These functions are used to converts a value(of any datatype) into a specified datatype.

What is CAST () in SQL? ›

The SQL CAST function converts the data type of an expression to the specified data type. For a list of the data types supported by InterSystems SQL, see Data Types. CAST is similar to CONVERT, with these differences: CONVERT is more flexible than CAST.

What is the difference between CAST and convert in MySQL? ›

MySQL CAST allows you to cast data from one data type to another data type. Here's our detailed article about MySQL CAST. MySQL CONVERT also allows you to convert data from one data type to another data type. It also allows you to convert character set of data into another character set.

What is To_char in SQL? ›

The TO_CHAR function converts DATETIME or DATE values to character string values. The TO_CHAR function evaluates a DATETIME value according to the date-formatting directive that you specify and returns an NVARCHAR value.

What is the disadvantage of type casting? ›

Disadvantage of Explicit Type casting in Java

When you will use type casting in Java, you can lose some information or data. Accuracy can be lost while using type casting. When a double is cast to an int, the fractional part of a double is discarded which causes the loss of fractional part of data.

What are the dangers of typecasting? ›

You might permanently change the value of the variable. C. You might temporarily lose part of the data - such as truncating a float when typecasting to an int.

Which data type Cannot be type cast? ›

But boolean primitive type can never be used in a cast.

What is the difference between try CAST and try convert? ›

TRY_CONVERT converts source value to target data type. But here you can use style as optional parameter to format the date. TRY_CAST do the same job like TRY_CONVERT except style as extra parameter. Here you can't pass style param.

What is difference between CAST and TRY_CAST? ›

A special version of CAST , :: that is available for a subset of data type conversions. It performs the same operation (i.e. converts a value of one data type into another data type), but returns a NULL value instead of raising an error when the conversion can not be performed.

Is conversion function in SQL? ›

SQL Conversion functions are single row functions which are capable of typecasting column value, literal or an expression . TO_CHAR, TO_NUMBER and TO_DATE are the three functions which perform cross modification of data types.

What is the rule for converting? ›

Basic Conversion Rule

The basic rule is: If you need to convert from a larger unit to a smaller unit, multiply. If you need to convert from a smaller unit to a larger unit, divide. You will make the number smaller and, as you already know, division is all about making numbers smaller.

What is a convert transaction? ›

Conversion Transaction means a merger, consolidation, recapitalization or other transaction to which the Purchaser is a party that results in the Purchaser Shares being converted into the right to receive cash or other securities.

What is CAST in T SQL? ›

The CAST() function converts a value (of any type) into a specified datatype. Tip: Also look at the CONVERT() function.

What is difference between CAST and Try_cast? ›

A special version of CAST , :: that is available for a subset of data type conversions. It performs the same operation (i.e. converts a value of one data type into another data type), but returns a NULL value instead of raising an error when the conversion can not be performed.

What is the purpose of using convert function ()? ›

The Microsoft Excel CONVERT function will convert a number from one measurement unit to another measurement unit. The CONVERT function is a built-in function in Excel that is categorized as an Engineering Function.

Why we use convert in SQL? ›

SQL CONVERT explicitly converts an expression of one data type to another with formatting. The function returns the converted value, if the conversion succeeds, or returns an error if it fails.

What is convert in SQL Server? ›

In SQL Server (Transact-SQL), the CONVERT function converts an expression from one datatype to another datatype. If the conversion fails, the function will return an error. Otherwise, it will return the converted value. TIP: Use the TRY_CONVERT function to return a NULL (instead of an error) if the conversion fails.

What is CAST with example? ›

: an impression taken from an object with a liquid or plastic substance : mold. They made a mask from a wax cast of her face.

What is the difference between try convert and try cast in SQL? ›

TRY_CONVERT converts source value to target data type. But here you can use style as optional parameter to format the date. TRY_CAST do the same job like TRY_CONVERT except style as extra parameter. Here you can't pass style param.

What is the difference between TRY_CAST and Try_convert in SQL Server? ›

TRY_CAST & TRY_CONVERT follow the same syntax as CAST and CONVERT but with one key difference: TRY_CAST and TRY_CONVERT return NULL if the conversion fails.

What is TRY_CAST in SQL Server? ›

TRY_CAST takes the value passed to it and tries to convert it to the specified data_type. If the cast succeeds, TRY_CAST returns the value as the specified data_type; if an error occurs, null is returned. However if you request a conversion that is explicitly not permitted, then TRY_CAST fails with an error.

What are the types of convert? ›

The possible types of conversion are standard conversion, no conversion, and user-defined nonstandard conversion.

Why do we need conversions? ›

Why are conversion rates important? Conversion rates are an effective way of comparing and contrasting the performance of multiple advertising channels. As with the example above, conversion rates are particularly important when running mobile user acquisition because they can measure the success of each campaign.

Why do we need to convert type? ›

Generally takes place when in an expression more than one data type is present. In such conditions type conversion (type promotion) takes place to avoid loss of data. C. All the data types of the variables are upgraded to the data type of the variable with the largest data type.

What is the process of CONVERT? ›

The conversion process searches for the first rule that matches the incoming data, and then applies the action associated with that rule. The output of that action can then be further processed by other rules, depending on its type-out value and whether other rules exist that are triggered by that value.

How to CONVERT values in SQL Server? ›

The CONVERT() function in SQL server is used to convert a value of one type to another type. It is the target data type to which the to expression will be converted, e.g: INT, BIT, SQL_VARIANT, etc. It provides the length of the target_type. Length is not mandatory.

Videos

1. Cast and Convert functions in SQL Server Part 28
(kudvenkat)
2. TSQL 12 CAST CONVERT
(Bhaskar Reddy Baddam)
3. SQL CAST VS CONVERT Function
(Lotusamaze)
4. Tutoriel Transact SQL 7 : Conversion Variables - CAST et CONVERT.
(The Arab Programmer - المبرمج العربي)
5. SQL Cast Function - Sql Training Online - Quick Tips Ep14
(Joey Blue)
6. SQL Server Cast and Convert Functions
(IT Port)
Top Articles
Latest Posts
Article information

Author: Roderick King

Last Updated: 03/04/2023

Views: 5862

Rating: 4 / 5 (51 voted)

Reviews: 82% of readers found this page helpful

Author information

Name: Roderick King

Birthday: 1997-10-09

Address: 3782 Madge Knoll, East Dudley, MA 63913

Phone: +2521695290067

Job: Customer Sales Coordinator

Hobby: Gunsmithing, Embroidery, Parkour, Kitesurfing, Rock climbing, Sand art, Beekeeping

Introduction: My name is Roderick King, I am a cute, splendid, excited, perfect, gentle, funny, vivacious person who loves writing and wants to share my knowledge and understanding with you.