sql что такое cast

Преобразование типов и оператор CAST стр. 2

Рассмотрим еще один пример.

Определить средний год спуска на воду кораблей из таблицы Ships.

А если нас интересует результат с заданной точностью, скажем, до двух десятичных знаков? Применение выражения CAST к среднему значению ничего не даст по указанной выше причине. Действительно,

вернет значение 1926.00. Следовательно, CAST нужно применить к аргументу агрегатной функции:

Результат — 1926.90909. Опять не то. Причина состоит в том, что при вычислении среднего значения было выполнено неявное преобразование типа. Сделаем еще один шаг:

В результате получим то, что нужно — 1926.91. Однако это решение выглядит очень громоздко. Заставим неявное преобразование типа поработать на нас:

Теперь мы использовали неявное преобразование целочисленного аргумента к точному числовому типу (EXACT NUMERIC), умножив его на вещественную единицу, после чего применили явное приведения типа результата агрегатной функции.

Функция CONVERT имеет следующий синтаксис:

Основное отличие функции CONVERT от функции CAST состоит в том, что первая позволяет форматировать данные (например, темпоральные данные типа datetime) при преобразовании их к символьному типу и указывать формат при обратном преобразовании. Разные целочисленные значения необязательного аргумента стиль соответствуют различным типам форматов. Рассмотрим следующий пример

Здесь мы преобразуем строковое представление даты к типу datetime, после чего выполняем обратное преобразование, чтобы продемонстрировать результат форматирования. Поскольку значение аргумента стиль не задано используется значение по умолчанию (0 или 100). В результате получим:

Jul 22 2003 12:00AM

Ниже приведены некоторые другие значения аргумента стиль и результат, полученный на приведенном выше примере. Заметим, что увеличение значения стиль на 100 приводит к четырехзначному отображению года.

Перечень всех возможных значений аргумента стиль можно посмотреть в BOL.

Источник

CAST and CONVERT (Transact-SQL)

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

Syntax

Transact-SQL Syntax Conventions

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

Arguments

data_type
The target data type. This includes xml, bigint, and sql_variant. Alias data types cannot 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.

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

Note: For a milliseconds (mmm) value of 0, the millisecond decimal fraction value will not display. For example, the value ‘2012-11-07T18:26:20.000 displays as ‘2012-11-07T18:26:20’.—127( 6, 7 )ISO8601 with time zone Z.yyyy-MM-ddThh:mm:ss.fffZ (no spaces)

Note: For a milliseconds (mmm) value of 0, the millisecond decimal value will not display. For example, the value ‘2012-11-07T18:26:20.000 will display as ‘2012-11-07T18:26:20’.—130 ( 1, 2 )Hijri ( 5 )dd mon yyyy hh:mi:ss:mmmAM

In this style, mon represents a multi-token Hijri unicode representation of the full month name. This value does not render correctly on a default US installation of SSMS.—131 ( 2 )Hijri ( 5 )dd/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).

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.

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

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.

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

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.

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.

xml styles

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

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.

Implicit conversions

Implicit conversions do not 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.

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

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:

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

Large-value data types

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.

text and image data types

The text and image data types do not 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 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 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.

* = Result length too short to display

E = 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:

Do not 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, SQL Server will sometimes return a truncated result value, and at other times it will return a rounded value. This table shows the behavior.

Results of the query are shown in the following table:

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:

1 With the exception of styles 20 and 21

Supplementary characters (surrogate pairs)

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.

1 Except for computed columns

Upgrading the database to compatibility level 110 and higher will not 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.

Examples

A. Using both CAST and CONVERT

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

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

Here is the result set.

C. Using CAST to concatenate

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

Here is the result set.

D. Using 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 AdventureWorksDW database.

Here is the result set.

E. Using CAST with the LIKE clause

Here is the result set.

F. Using 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):

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

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

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

Here is the result set.

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.

Here is the result set.

H. Using CONVERT with binary and character data

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

Here is the result set.

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

Here is the result set.

This example shows that Style 2 does not truncate the result, because the result does not include the characters 0x.

Here is the result set.

Convert the character value ‘Name’ to a binary value.

Here is the result set.

Here is the result set.

Here is the result set.

I. Converting date and time data types

This example shows the conversion of date, time, and datetime data types.

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

K. Effects of data type precedence in allowed conversions

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

Here is the result set.

The int value of 1 was converted to a VARCHAR.

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

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

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

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. Using 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 AdventureWorksDW2016 database.

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

M. Using 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 AdventureWorksDW2016 database.

Here is the result set.

N. Using 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 AdventureWorksDW2016 database.

O. Using 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 AdventureWorksDW2016 database.

Here is the result set.

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

Источник