tech@faiz
  CAST and CONVERT
 
SQL Server

CAST and CONVERT (Transact-SQL)

Converts an expression of one data type to another.

Transact-SQL Syntax Conventions

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

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

Is any valid expression.

data_type

Is the target data type. This includes xml, bigint, and sql_variant. Alias data types cannot be used. For more information about available data types, see Data Types (Transact-SQL).

length

Is an optional integer that specifies the length of the target data type. The default value is 30.

style

Is an integer expression that specifies how the CONVERT function is to translate expression. If style is NULL, NULL is returned. The range is determined by data_type. For more information, see the Remarks section.

Returns expression translated to data_type.

Date and Time Styles

When expression is a date or time data type, style can be one of the values shown in the following table. Other values are processed as 0. SQL Server supports the date format in Arabic style by using the Kuwaiti algorithm.

Without century (yy) (1) With century (yyyy) Standard Input/Output (3)

-

0 or 100 (1, 2)

Default

mon dd yyyy hh:miAM (or PM)

1

101

U.S.

mm/dd/yyyy

2

102

ANSI

yy.mm.dd

3

103

British/French

dd/mm/yyyy

4

104

German

dd.mm.yy

5

105

Italian

dd-mm-yy

6

106 (1)

-

dd mon yy

7

107 (1)

-

Mon dd, yy

8

108

-

hh:mi:ss

-

9 or 109 (1, 2)

Default + milliseconds

mon dd yyyy hh:mi:ss:mmmAM (or PM)

10

110

USA

mm-dd-yy

11

111

JAPAN

yy/mm/dd

12

112

ISO

yymmdd

yyyymmdd

-

13 or 113 (1, 2)

Europe default + milliseconds

dd mon yyyy hh:mi:ss:mmm(24h)

14

114

-

hh:mi:ss:mmm(24h)

-

20 or 120 (2)

ODBC canonical

yyyy-mm-dd hh:mi:ss(24h)

-

21 or 121 (2)

ODBC canonical (with milliseconds)

yyyy-mm-dd hh:mi:ss.mmm(24h)

-

126 (4)

ISO8601

yyyy-mm-ddThh:mi:ss.mmm (no spaces)

-

127(6, 7)

ISO8601 with time zone Z.

yyyy-mm-ddThh:mi:ss.mmmZ

(no spaces)

-

130 (1, 2)

Hijri (5)

dd mon yyyy hh:mi:ss:mmmAM

-

131 (2)

Hijri (5)

dd/mm/yy 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 (style 0 or 100, 9 or 109, 13 or 113, 20 or 120, and 21 or 121) always return the century (yyyy).

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, the output format is as described in the previous table.

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

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

6 Only supported when casting from character data to datetime or smalldatetime. When character data that represents only date or only time components is cast 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.

7The optional time zone indicator, Z, is used 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 is the indicator for time zone UTC-0. Other time zones are indicated with HH:MM offset in the + or - direction. For example: 2006-12-12T23:45:12-08:00.

When you convert to character data from smalldatetime, the styles that include seconds or milliseconds show zeros in these positions. You can truncate unwanted date parts when you convert from datetime or smalldatetime values by using an appropriate char or varchar data type length.

When you convert to datetimeoffset from character data with a style that includes a time, a time zone offset is appended to the result.

float and real Styles

When expression is float or real, style can be one of the values shown in the following table. Other values are processed as 0.

Value Output

0 (default)

A maximum of 6 digits. Use in scientific notation, when appropriate.

1

Always 8 digits. Always use in scientific notation.

2

Always 16 digits. Always use in scientific notation.

126, 128, 129

Included for legacy reasons and might be deprecated in a future release.

money and smallmoney Styles

When expression is money or smallmoney, style can be one of the values shown in the following table. Other values are processed as 0.

Value Output

0 (default)

No commas every three digits to the left of the decimal point, and two digits to the right of the decimal point; for example, 4235.98.

1

Commas every three digits to the left of the decimal point, and two digits to the right of the decimal point; for example, 3,510.92.

2

No commas every three digits to the left of the decimal point, and four digits to the right of the decimal point; for example, 4235.9819.

126

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

xml Styles

When expression is xml, style can be one of the values shown in the following table. Other values are processed as 0.

Value Output

0 (default)

Use default parsing behavior that discards insignificant white space and does not allow for an internal DTD subset.

Note:
When you convert to the xml data type, SQL Server insignificant white space is handled differently than in XML 1.0. For more information, see Generating XML Instances.

1

Preserve insignificant white space. This style setting sets the default xml:space handling to behave the same as if xml:space="preserve" has been specified instead.

2

Enable 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 will be checked for syntactical correctness.
     

The parser will ignore external DTD subsets. It also does not evaluate the XML declaration to see whether the standalone attribute is set yes or no, but instead parses the XML instance as if it is a stand-alone document.

3

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

Binary Styles

When expression is binary(n), varbinary(n), char(n), or varchar(n), style can be one of the values shown in the following table. Style values that are not listed in the table return an error.

Value Output

0 (default)

Translates ASCII characters to binary bytes or binary bytes to ASCII characters. Each character or byte is converted 1:1.

If the data_type is a binary type, the characters 0x are added to the left of the result.

1, 2

If the data_type is a binary type, the expression must be a character expression. The expression must be composed of 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 characters 0x must be the first two characters in the expression. If the expression contains an odd number of characters or if any of the characters are invalid an error is raised.

If the length of the converted expression is greater than the length of the data_type the result will be right truncated.

Fixed length data_types that are larger then the converted result will have zeros added to the right of the result.

If the data_type is a character type, the expression must be a binary expression. Each binary character is converted into two hexadecimal characters. If the length of the converted expression is greater than the data_type length it will be right truncated.

If the data_type is a fix sized character type and 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 will be added to the left of the converted result for style 1.

Implicit Conversions

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 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.


When you convert between datetimeoffset and the character types char, varchar, nchar, and nvarchar the converted time zone offset part should always be double digits for both HH and MM for example, -08:00.
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:

  • Conversion from image to varbinary(max) and vice-versa is an implicit conversion, and so are 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 will occur if the large value is too big for the specified length of the smaller data type.
     
  • Conversion from varchar, nvarchar, or varbinary to their corresponding large-value data types is performed implicitly.
     
  • Conversion from the sql_variant data type to the large-value data types is an explicit conversion.
     
  • Large-value data types cannot be converted to the sql_variant data type. 

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 how to convert 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.

From data type To data type Result

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:

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.

Here is the result set.

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.

From To 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.

 

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

(5 row(s) affected)

 

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

 

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

Certain datetime Conversions Are Nondeterministic in SQL Server 2005 and Later Versions

In SQL Server 2000, string to date and time conversions are marked as deterministic. However, this is not true for the styles listed in the following table. For these styles, the conversions depend on the language settings. SQL Server 2005 and later versions mark these conversions as nondeterministic.

The following table lists the styles for which the string-to-datetime conversion is nondeterministic.

All styles below 1001

106

107

109

113

130

1 With the exception of styles 20 and 21



 
  Today, there have been 1 visitors (2 hits) on this page!  
 
Free Domain This site was last updated Monday, 23 January 2017
Copyright © 2006-2017 smfaizhaider. All rights reserved.