A RetroSearch Logo

Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Search Query:

Showing content from https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2005/ms187928(v=sql.90) below:

CAST and CONVERT (Transact-SQL) | Microsoft Learn

Explicitly converts an expression of one data type to another. CAST and CONVERT provide similar functionality.

Transact-SQL Syntax Conventions

Syntax 
Syntax for CAST:
CAST ( expression AS data_type [ (length ) ])

Syntax for CONVERT:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
Arguments Return Types

Returns the same value as data_type.

Implicit conversions are those conversions that occur without specifying either the CAST or CONVERT function. Explicit conversions are those conversions that require the CAST or CONVERT function to be specified. The following illustration shows all explicit and implicit data type conversions that are allowed for SQL Server 2005 system-supplied data types. These include xml, bigint, and sql_variant. There is no implicit conversion on assignment from the sql_variant data type, but there is implicit conversion to sql_variant.

Note

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 does not return a hexadecimal value of 41; it returns 4100: SELECT CAST(CAST(0x41 AS nvarchar) AS varbinary).

Large-value Data Types

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

For information about converting Microsoft .NET Framework common language runtime (CLR) user-defined types, see Performing Operations on User-defined Types. For more information about converting from the xml data type, see Generating XML Instances.

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 set of rules. For information about these rules, see Serialization of XML Data. For information on how to cast from XML to a CLR user-defined type, see Performing Operations on User-defined Types. For information about converting from other data types to the xml data type, see Generating XML Instances.

text and image Data Types

Automatic data type conversion is not supported for the text and image data types. 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 such as trying to convert a character expression that includes letters to an int, SQL Server returns an error message.

Output Collation

When the output of CAST or CONVERT is a character string, and the input is a character string, the output has the same collation and collation label as the input. If the input is not 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 you convert character or binary expressions (char, nchar, nvarchar, varchar, binary, or varbinary) to an expression of a different data type, data can be truncated, only partially displayed, or an error is returned because the result is too short to display. Conversions to char, varchar, nchar, nvarchar, binary, and varbinary are truncated, except for the conversions shown in the following table.

int, smallint, or tinyint

char

*

varchar

*

nchar

E

nvarchar

E

money, smallmoney, numeric, decimal, float, or real

char

E

varchar

E

nchar

E

nvarchar

E

* = Result length too short to display. E = Error returned because result length is too short to display.

SQL Server guarantees that only roundtrip conversions, conversions that convert a data type from its original data type and back again, will 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 CONVERT
SELECT CONVERT(decimal(10,5), CONVERT(varbinary(20), @myval))

Note

Do not try to 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 AdventureWorks;
GO
SELECT c.FirstName, c.LastName, SUBSTRING(c.Title, 1, 25) AS Title, CAST(e.SickLeaveHours AS char(1)) AS 'Sick Leave'
FROM HumanResources.Employee e JOIN Person.Contact c ON e.EmployeeID = c. ContactID
WHERE NOT EmployeeID >5

Here is the result set.

FirstName      LastName         Title                  Sick Leave
---------      ---------      -------------------   -----------
Gustavo        Achong         Mr.                   *
Catherine      Abel           Ms.                   *
Kim            Abercrombie    Ms.                   *
Humberto       Acevedo        Sr.                   *
Pilar          Ackerman       Sra.                  *

(5 row(s) affected)

When you convert data types that differ in decimal places, sometimes the result value is truncated and at other times it is rounded. The following table shows the 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

For example, the result of the following conversion is 10:

SELECT CAST(10.6496 AS int)

When you convert data types in which the target data type has fewer decimal places than the source data type, the value is rounded. For example, the result of the following conversion is $10.3497:

SELECT CAST(10.3496847 AS money)

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

Converting Binary String Data

When binary or varbinary data is converted to character data and an odd number of values is specified following the x, SQL Server adds a 0 (zero) after the x to make an even number of values.

Binary data is made up of the characters from 0 through 9 and from A through F, or from a through f, in groups of two characters each. Binary strings must be preceded by 0x. For example, to input FF, type 0xFF. The maximum value is a binary value of 8000 bytes, each of which is FF. The binary data types are not for hexadecimal data but instead for bit patterns. Conversions and calculations of hexadecimal numbers stored as binary data can be unreliable.

When you specify the length of a binary data type, every two characters count as one. A length of 10 signifies that 10 two-character groupings will be entered.

Empty binary strings are represented by 0x and can be stored as binary data.

Examples A. Using both CAST and CONVERT

Each example retrieves the name of the product for those products that have a 3 in the first digit of their list price and converts their ListPrice to int.

-- Use CAST
USE AdventureWorks;
GO
SELECT SUBSTRING(Name, 1, 30) AS ProductName, ListPrice
FROM Production.Product
WHERE CAST(ListPrice AS int) LIKE '3%';
GO

-- Use CONVERT.
USE AdventureWorks;
GO
SELECT SUBSTRING(Name, 1, 30) AS ProductName, ListPrice
FROM Production.Product
WHERE CONVERT(int, ListPrice) LIKE '3%';
GO
B. Using CAST with arithmetic operators

The following example calculates a single column computation (Computed) by dividing the total year-to-date sales (SalesYTD) by the commission percentage (CommissionPCT). This result is converted to an int data type after being rounded to the nearest whole number.

USE AdventureWorks;
GO
SELECT CAST(ROUND(SalesYTD/CommissionPCT, 0) AS int) AS 'Computed'
FROM Sales.SalesPerson 
WHERE CommissionPCT != 0;
GO

Here is the result set.

Computed      
------ 
379753754
346698349
257144242
176493899
281101272
0
301872549
212623750
298948202
250784119
239246890
101664220
124511336
97688107

(14 row(s) affected)
C. Using CAST to concatenate

The following example concatenates noncharacter, nonbinary expressions by using CAST.

USE AdventureWorks;
GO
SELECT 'The list price is ' + CAST(ListPrice AS varchar(12)) AS ListPrice
FROM Production.Product
WHERE ListPrice BETWEEN 350.00 AND 400.00;
GO

Here is the 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.09

(5 row(s) affected)
D. Using CAST to produce more readable text

The following example uses CAST in the select list to convert the Name column to a char(10) column.

USE AdventureWorks;
GO
SELECT DISTINCT CAST(p.Name AS char(10)) AS Name, s.UnitPrice
FROM Sales.SalesOrderDetail s JOIN Production.Product p on s.ProductID = p.ProductID
WHERE Name LIKE 'Long-Sleeve Logo Jersey, M';
GO

Here is the result set.

Name       UnitPrice
---------- ---------------------
Long-Sleev 31.2437
Long-Sleev 32.4935
Long-Sleev 49.99

(3 row(s) affected)
E. Using CAST with the LIKE clause

The following example converts the money column SalesYTD to an int and then to a char(20) column so that it can be used with the LIKE clause.

USE AdventureWorks;
GO
SELECT p.FirstName, p.LastName, s.SalesYTD, s.SalesPersonID
FROM Person.Contact p JOIN Sales.SalesPerson s ON p.ContactID = s.SalesPersonID
WHERE CAST(CAST(s.SalesYTD AS int) AS char(20)) LIKE '2%';
GO

Here is the result set.

FirstName        LastName            SalesYTD         SalesPersonID
---------------- ------------------- ---------------- -------------
Carol            Elliott             2811012.7151      279
Julie            Estes               219088.8836       288
Janeth           Esteves             2241204.0424      289

(3 row(s) affected)
F. Using CONVERT or CAST with typed XML

The following are several examples that show using CONVERT to convert to typed XML by using the xml data type.

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):

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):

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

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

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

For more examples, see Generating XML Instances.

G. Using CAST and CONVERT with datetime data

The following 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 display the date and time in the ISO 8901 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

----------------------- ------------------------------ ------------------------------

2006-04-18 09:58:04.570 Apr 18 2006 9:58AM            2006-04-18T09:58:04.570

(1 row(s) affected)

The following example is approximately the opposite of the previous example. The 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-04T15:50:59.997' AS UnconvertedText,
   CAST('2006-04-04T15:50:59.997' AS datetime) AS UsingCast,
   CONVERT(datetime, '2006-04-04T15:50:59.997', 126) AS UsingConvertFrom_ISO8601 ;
GO

Here is the result set.

UnconvertedText         UsingCast               UsingConvertFrom_ISO8601

----------------------- ----------------------- ------------------------

2006-04-04T15:50:59.997 2006-04-04 15:50:59.997 2006-04-04 15:50:59.997

(1 row(s) affected)

See Also Reference

SELECT (Transact-SQL)
System Functions (Transact-SQL)

Other Resources

Data Type Conversion (Database Engine)
ISO 8601 Format
Writing International Transact-SQL Statements

Help and Information

Getting SQL Server 2005 Assistance

Change History

17 July 2006

New content:

14 April 2006

New content:

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.3