Функции CAST и CONVERT (Transact-SQL)

Если параметр expression принадлежит к типу данных date или time, параметр style может иметь одно из значений, приведенных в следующей таблице. Другие значения обрабатываются как 0. SQL Server поддерживает формат даты в арабском стиле, используя кувейтский алгоритм.

1 Эти значения стилей возвращают недетерминированные результаты. Включают в себя все стили «гг» (без номера века) и часть стилей «гггг» (с номером века).

2 Значения по умолчанию (style0 или 100, 9 или 109, 13 или 113, 20 или 120 и 21 или 121) всегда возвращают год с веком (гггг).

3 Вход при преобразовании в тип datetime; выход при преобразовании в символьные данные.

4 Для использования в формате XML. Для преобразования из datetime или smalldatetime в символьные данные формат вывода должен быть таким как это описано в предыдущей таблице.

5 Хиджра — календарная система с несколькими вариантами. В SQL Server используется кувейтский алгоритм.

Важно!

По умолчанию SQL Server интерпретирует двузначные значения года с пороговым значением 2049. Т.е. год, обозначенный двумя цифрами 49, интерпретируется как 2049, а год, обозначенный двумя цифрами 50, интерпретируется как 1950. Многие клиентские приложения, в частности основанные на объектах OLE-автоматизации, используют для этого значение 2030. В SQL Server есть параметр конфигурации two digit year cutoff, изменяющий пороговое значение года SQL Server, что позволяет согласовывать работу с датами. Рекомендуется использовать четырехзначные года.

6 Поддерживается только при приведении символьных данных к типу datetime или smalldatetime. При приведении символьных данных, представляющих только дату или только время, к типам datetime и smalldatetime неуказанное время устанавливается в 00:00:00.000, а неуказанная дата — в 1900-01-01.

7 Необязательный признак часового пояса П используется для упрощения сопоставления XML-значений типа datetime, содержащих сведения о часовом поясе, со значениями SQL Server типа datetime без таких сведений. П — это индикатор часового пояса UTC-0. Другие часовые пояса обозначаются смещением в формате ЧЧ:ММ в направлении + или -. Например: 2006-12-12T23:45:12-08:00.

При преобразовании в символьные данные из smalldatetime стили, включающие секунды или миллисекунды, будут содержать нули в соответствующих позициях. При преобразовании из datetime или smalldatetime ненужные части даты можно усекать с помощью типа данных char или varchar соответствующей длины.

При преобразовании в тип данных datetimeoffset из символьных данных со стилем, включающим время, смещение часового пояса добавляется к результату.

Если expression равен float или real, то параметр style может иметь один из типов, перечисленных в следующей таблице. Другие значения обрабатываются как 0.

Если expression равен money или smallmoney, то параметр style может иметь один из типов, перечисленных в следующей таблице. Другие значения обрабатываются как 0.

Если выражение expression имеет тип xml, style может принимать одно из значений, приведенных в следующей таблице. Другие значения обрабатываются как 0.

Значение	Выходные данные
0 (по умолчанию)	Использовать синтаксический разбор по умолчанию, при котором незначащие пробельные символы удаляются, а внутренние подмножества DTD не разрешены. Примечание При преобразовании к типу данных xml SQL Server обрабатывает незначащие пробельные символы не так, как это описано в стандарте XML 1.0. Дополнительные сведения см. в разделе Формирование XML-экземпляров.
1	Сохранять незначащие пробельные символы. Это значение стиля приводит к тому, что обработка атрибута xml:space производится так же, как если бы было указано xml:space="preserve".
2	Использовать ограниченную обработку внутреннего подмножества DTD. При этом для выполнения операций синтаксического анализа без проверки действительности сервер может пользоваться следующей информацией, предоставляемой внутренним подмножеством DTD. Применяются атрибуты по умолчанию. Ссылки на внутренние сущности разрешаются и раскрываются. Будет проверена синтаксическая правильность модели содержимого DTD. Синтаксический анализатор будет пропускать внешние подмножества DTD. Он также не будет рассматривать XML-декларацию, чтобы проверить, присвоено ли атрибуту standalone значение yes или no, вместо этого экземпляр XML будет анализироваться как изолированный документ.
3	Сохранять незначащие пробельные символы и использовать ограниченную обработку внутреннего подмножества DTD.

Если параметр expression имеет значение binary(n), varbinary(n), char(n) или varchar(n), параметр style может принимать одно из значений, приведенных в следующей таблице. При использовании значений стиля, отсутствующих в этой таблице, возвращается ошибка.

Неявные преобразования происходят, когда функции CAST или CONVERT не вызываются. Явные преобразования требуют вызова функции CAST или CONVERT. На следующей иллюстрации показаны все явные и неявные преобразования типов данных, допустимые для системных типов данных SQL Server. Сюда входят типы xml, bigint и sql_variant. При присваивании неявного преобразования от типа sql_variant не происходит, но неявное преобразование к типу sql_variant производится.

При преобразованиях между типом datetimeoffset и символьными типами char, varchar, nchar и nvarchar преобразованная часть смещения часового пояса должна иметь по два разряда для часов и минут, например -08:00.

Примечание
Так как у данных в Юникоде всегда четное число байтов, будьте осторожны при преобразовании значений типа binary или varbinary к типам данных, поддерживающим Юникод, и наоборот. Например, следующее преобразование не вернет шестнадцатеричное значение 41, оно вернет 4100: SELECT CAST(CAST(0x41 AS nvarchar) AS varbinary).

Типы данных большого объема демонстрируют то же поведение при явных и неявных преобразованиях, что и их аналоги меньшего объема, а именно типы данных varchar, nvarchar и varbinary. Однако необходимо учитывать следующие правила:

• преобразование из image в varbinary(max) и обратно неявное, как и преобразования между text и varchar(max) и ntext и nvarchar(max);

• преобразование из типов данных большого объема, например varchar(max), в аналогичный тип данных меньшего объема, например varchar, неявное, но если объем данных слишком велик, будет произведено усечение данных до указанной длины конкретного типа данных меньшего объема;

• преобразование из varchar, nvarchar или varbinary в соответствующие им типы данных большого объема выполняется неявно;

• преобразование из типа данных sql_variant в типы данных большого объема выполняется явно.

• типы данных большого объема не могут быть преобразованы в тип данных sql_variant.

Сведения о преобразовании определяемых пользователем типов данных CLR платформы Microsoft .NET Framework см. в разделе Выполнение операций над определяемыми пользователем типами данных. Дополнительные сведения о преобразовании из типа данных xml см. в разделе Формирование XML-экземпляров.

При явном или неявном приведении типа данных xml к строковому или двоичному типу данных, содержимое типа данных xml сериализуется согласно набору определенных правил. Сведения об этих правилах см. в разделе Сериализация XML-данных. Сведения о приведении типа данных XML к пользовательскому типу данных CLR см. в разделе Выполнение операций над определяемыми пользователем типами данных. Сведения о преобразовании из других типов данных к типу данных xml см. в разделе Формирование XML-экземпляров.

Для типов данных text и image автоматическое преобразование типов не поддерживается. Можно явно преобразовать text в символьные данные, а image — в binary или varbinary, но длиной не более 8 000 байт. Если вы попробуете произвести неверное преобразование, например преобразовать символьное выражение, содержащее буквы, в int, SQL Server вернет ошибку.

Если входные и выходные данные функций CAST и CONVERT — символьные строки, у выходных данных будут те же параметры сортировки, что и у входных. Если входные данные не символьная строка, выходным назначаются параметры сортировки по умолчанию для этой базы данных и присваивается метка о том, что этот режим используется принудительно. Дополнительные сведения см. в разделе Очередность параметров сортировки (Transact-SQL).

Чтобы назначить выходным данным другие параметры сортировки, примените предложение COLLATE к результирующему выражению функции CAST или CONVERT. Например:

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

При преобразовании символьных или двоичных выражений (char, nchar, nvarchar, varchar, binary или varbinary) к выражению другого типа данных данные могут быть усечены, отображаться только частично или вызывать ошибку, так как результат слишком мал для отображения. Результаты преобразований в char, varchar, nchar, nvarchar, binary и varbinary усекаются всегда, за исключением случаев, перечисленных в таблице ниже.

* = результат слишком мал для отображения. О = ошибка, так как длина результата слишком мала для отображения.

SQL Server гарантирует получение одинаковых результатов в разных версиях только для обратимых преобразований, то есть таких, когда данные преобразуются из исходного типа данных, а затем опять в него. В следующем примере показано обратимое преобразование:

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

Примечание
Не пытайтесь составлять данные типа binary, а затем преобразовывать их к данным численного типа. SQL Server не гарантирует, что результат преобразования типа данных decimal или numeric в binary будет одинаковым в разных версиях SQL Server.

В следующем примере показано результирующее выражение, которое слишком мало для отображения.

USE AdventureWorks2008R2;
GO
SELECT p.FirstName, p.LastName, SUBSTRING(p.Title, 1, 25) AS Title, CAST(e.SickLeaveHours AS char(1)) AS 'Sick Leave'
FROM HumanResources.Employee e 
JOIN Person.Person p ON e.BusinessEntityID = p.BusinessEntityID
WHERE NOT e.BusinessEntityID >5;

Ниже приводится результирующий набор.

FirstName     LastName       Title       Sick Leave

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

Ken           Sanchez        NULL        *

Terri         Duffy          NULL        *

Roberto       Tamburello     NULL        *

Rob           Walters        NULL        *

Gail          Erickson       Ms.         *

(5 row(s) affected)

При преобразовании между типами данных с разными длинами дробных частей результат может усекаться или округляться. В следующей таблице описано это поведение.

Например, результатом следующего преобразования будет 10:

SELECT CAST(10.6496 AS int)

При преобразовании к типам данных, у которых дробная часть короче, чем у исходного типа, значение округляется. Например, результатом следующего преобразования будет $10.3497:

SELECT CAST(10.3496847 AS money)

SQL Server возвращает сообщение об ошибке при попытке преобразовать нечисловые данные типа char, nchar, varchar или nvarchar к типам int, float, numeric и decimal. SQL Server также возвращает ошибку, если к типам numeric и decimal преобразуется пустая строка (« »).

В SQL Server 2000 преобразования строковых значений в значения date и time помечаются как детерминированные. Однако это неверно для стилей, перечисленных в следующей таблице. Для этих стилей преобразование зависит от языковых параметров. В SQL Server 2005 и более поздних версиях эти преобразования отмечаются как недетерминированные.

Следующая таблица содержит стили, для которых преобразование строк в тип datetime недетерминировано.

1 За исключением стилей 20 и 21.

В каждом примере получаются имена продуктов, у которых первая цифра цены — 3, для этого их значения ListPrice преобразовываются к int.

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

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

В следующем примере вычисляется столбец значений (Computed) путем деления суммарных продаж за год (SalesYTD) на проценты комиссионных (CommissionPCT). Результат преобразуется к типу данных int после округления до ближайшего целого числа.

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

Ниже приводится результирующий набор.

Computed

------

379753754

346698349

257144242

176493899

281101272

0

301872549

212623750

298948202

250784119

239246890

101664220

124511336

97688107

(14 row(s) affected)

В следующем примере несимвольные недвоичные выражения объединяются с помощью функции CAST.

USE AdventureWorks2008R2;
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

Ниже приводится результирующий набор.

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)

В следующем примере функция CAST в списке выбора используется для преобразования значений столбца Name к значениям типа char(10).

USE AdventureWorks2008R2;
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

Ниже приводится результирующий набор.

Name       UnitPrice

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

Long-Sleev 31.2437

Long-Sleev 32.4935

Long-Sleev 49.99

(3 row(s) affected)

В следующем примере столбец значений money типа SalesYTD преобразуется в тип int, а затем в тип char(20) так, чтобы его можно было использовать в предложении LIKE.

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

Ниже приводится результирующий набор.

FirstName        LastName            SalesYTD         SalesPersonID

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

Tsvi             Reiter              2811012.7151      279

Syed             Abbas               219088.8836       288

Rachel           Valdez              2241204.0424      289

(3 row(s) affected)

В следующих нескольких примерах показано использование функции CONVERT для преобразования данных в типизированный XML-код при помощи типа данных xml.

В этом примере строка, содержащая пробельные символы, текст и разметку, преобразуется в типизированный XML, в котором удаляются все незначащие пробельные символы (пробелы, разделяющие узлы):

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

В этом примере похожая строка, содержащая пробельные символы, текст и разметку, преобразуется в типизированный XML, в котором сохраняются все незначащие пробельные символы (пробелы, разделяющие узлы):

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

В этом примере строка, содержащая пробельные символы, текст и разметку, приводится к типизированному XML:

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

Дополнительные примеры см. в разделе Формирование XML-экземпляров.

Следующий пример показывает текущие дату и время, использует функцию CAST для изменения текущих даты и времени в символьный тип данных и затем использует CONVERT для отображения даты и времени в формате ISO 8901.

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

Ниже приводится результирующий набор.

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)

Следующий пример — частичная противоположность предыдущему примеру. Пример отображает дату и время в виде символьных данных, использует функцию CAST для изменения символьных данных в тип данных datetime, а затем использует CONVERT для изменения символьных данных в тип данных datetime.

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

Ниже приводится результирующий набор.

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)

В следующих примерах показаны результаты преобразования двоичных и символьных данных с использованием различных стилей.

--Convert the binary value 0x4E616d65 to a character value.
SELECT CONVERT(char(8), 0x4E616d65, 0) AS 'Style 0, binary to character'

Ниже приводится результирующий набор.

Style 0, binary to character

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

Name

(1 row(s) affected)

--The following example shows how Style 1 can force the result
--to be truncated. The truncation is caused by
--including the characters 0x in the result.
SELECT CONVERT(char(8), 0x4E616d65, 1) AS 'Style 1, binary to character'

Ниже приводится результирующий набор.

Style 1, binary to character

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

0x4E616D

(1 row(s) affected)

--The following example shows that Style 2 does not truncate the
--result because the characters 0x are not included in
--the result.
SELECT CONVERT(char(8), 0x4E616d65, 2) AS 'Style 2, binary to character'

Ниже приводится результирующий набор.

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'

Ниже приводится результирующий набор.

Style 0, character to binary

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

0x4E616D6500000000

(1 row(s) affected)

SELECT CONVERT(binary(4), '0x4E616D65', 1) AS 'Style 1, character to binary'

Ниже приводится результирующий набор.

Style 1, character to binary

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

0x4E616D65

(1 row(s) affected)

SELECT CONVERT(binary(4), '4E616D65', 2) AS 'Style 2, character to binary'

Ниже приводится результирующий набор.

Style 2, character to binary

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

0x4E616D65

(1 row(s) affected)