Функции CAST и CONVERT (Transact-SQL)
Применимо к:
SQL Server Azure SQL DatabaseУправляемый экземпляр SQL AzureAzure Synapse Analytics AnalyticsPlatform System (PDW)
Эти функции преобразуют выражение одного типа данных в другой.
Синтаксис
CAST Синтаксис:
CAST ( expression AS data_type [ ( length ) ] )
CONVERT Синтаксис:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
Соглашения о синтаксисе Transact-SQL
Примечание
Ссылки на описание синтаксиса Transact-SQL для SQL Server 2014 и более ранних версий, см. в статье Документация по предыдущим версиям.
Аргументы
expression
Любое допустимое выражение.
data_type
Целевой тип данных. Это может быть xml, bigint и sql_variant. Типы данных псевдонимов нельзя использовать.
length
Указываемое дополнительно целое число, обозначающее длину целевого типа данных, который допускает указание длины пользователем. Значение по умолчанию — 30.
style
Целочисленное выражение, указывающее, как CONVERT функция будет переводить выражение. Для значения стиля NULL возвращается NULL. Аргумент data_type определяет диапазон.
Типы возвращаемых данных
Возвращает значение аргумента expression, преобразованное в тип data_type.
Стили даты и времени
Если аргумент expression принадлежит к типу данных даты или времени, аргумент style может иметь одно из значений, приведенных в таблице ниже. Другие значения обрабатываются как 0. Начиная с SQL Server 2012 (11.x), преобразование типов даты и времени в datetimeoffset поддерживается только для стилей 0 и 1. Все другие стили преобразования возвращают ошибку 9809.
Примечание
SQL Server поддерживает формат даты в арабском стиле, используя кувейтский алгоритм.
| Без века (гг) 1 | С веком (гггг) | Standard | Входные и выходные данные 3 |
|---|---|---|---|
| - | 0 или 1001,2 | Значения по умолчанию для datetime и smalldatetime | mon dd yyyy hh:miAM (или PM) |
| 1 | 101 | США | 1 = mm/dd/yy101 = mm/dd/yyyy |
| 2 | 102 | ANSI | 2 = yy.mm.dd102 = yyyy.mm.dd |
| 3 | 103 | Британский/французский | 3 = dd/mm/yy103 = dd/mm/yyyy |
| 4 | 104 | Немецкий | 4 = dd.mm.yy104 = dd.mm.yyyy |
| 5 | 105 | Итальянский | 5 = dd-mm-yy105 = dd-mm-yyyy |
| 6 | 1061 | - | 6 = dd mon yy106 = dd mon yyyy |
| 7 | 1071 | - | 7 = Mon dd, yy107 = Mon dd, yyyy |
| 8 или 24 | 108 | - | hh:mi:ss |
| - | 9 или 1091,2 | По умолчанию + миллисекунды | mon dd yyyy hh:mi:ss:mmmAM (или PM) |
| 10 | 110 | США | 10 = мм-дд-гг 110 = mm-dd-yyyy |
| 11 | 111 | Япония | 11 = гг/мм/дд 111 = yyyy/mm/dd |
| 12 | 112 | ISO | 12 = ггммдд 112 = yyyymmdd |
| - | 13 или 1131,2 | Европейский по умолчанию + миллисекунды | dd mon yyyy hh:mi:ss:mmm (24 часа) |
| 14 | 114 | - | hh:mi:ss:mmm (24 часа) |
| - | 20 или 1202 | Канонический формат ODBC | yyyy-mm-dd hh:mi:ss (24 часа) |
| - | 21 , 25 или 1212 | Каноническая версия ODBC (с миллисекундами) по умолчанию для времени, даты, даты и времени2 и datetimeoffset | yyyy-mm-dd hh:mi:ss.mmm (24 часа) |
| 22 | - | США | mm/dd/yy hh:mi:ss AM (или PM) |
| - | 23 | ISO8601 | yyyy-mm-dd |
| - | 1264 | ISO8601 | yyyy-mm-ddThh:mi:ss.mmm (без пробелов) 6 |
| - | 1278, 9 | ISO8601 с часовой поясом Z | yyyy-MM-ddThh:mm:ss.fffZ (без пробелов) 6 |
| - | 1301,2 | Хиджра 5 | dd mon yyyy hh:mi:ss:mmmAM7 |
| - | 1312 | Хиджра 5 | dd/mm/yyyy hh:mi:ss:mmmAM |
1 Эти значения стилей возвращают недетерминированные результаты. Включает все стили (yy) (без века) и подмножество стилей () (yyyyс веками).
2 Значения по умолчанию (0 или 100, 9 или 109, 13 или 113, 20 или 120, 23 и 21 , 25 или 121) всегда возвращают век (yyyy).
Важно!
По умолчанию SQL Server интерпретирует двузначные значения года с пороговым значением 2049. Это означает, что SQL Server интерпретирует двухзначное значение года 49 как 2049, а двухзначное значение 50 — как 1950. В большинстве клиентских приложений, основанных, в частности, на объектах автоматизации, 2030 год используется в качестве порогового значения. SQL Server предоставляет параметр настройки двузначного порогового значения года, с помощью которого можно изменить пороговое значение года, используемое в SQL Server. Это позволяет обеспечить согласованную обработку дат. Рекомендуется использовать четырехзначные года.
3 Вход при преобразовании в тип datetime; выход при преобразовании в символьные данные.
4 Для использования в формате XML. Для преобразования из datetime или smalldatetime в символьные данные формат вывода должен быть таким, как описано в предыдущей таблице.
5 Хиджра — календарная система с несколькими вариантами. В SQL Server используется кувейтский алгоритм.
6 Для значения в миллисекундах (mmm) 0 значение десятичной дроби миллисекунды не отображается. Например, значение 2022-11-07T18:26:20.000 отображается как 2022-11-07T18:26:20.
7 В этом стиле mon представляет представление юникода хиджра с несколькими токенами полного названия месяца. Это значение не отображается правильно при установке SSMS по умолчанию в США.
8 Поддерживается только при приведение символьных данных к datetime или smalldatetime. При приведение символьных данных, представляющих только компоненты даты или времени, к типам данных datetime или smalldatetime , для неопределенного компонента времени устанавливается значение 00:00:00.000, а для компонента неустановленной даты — значение 1900-01-01.
9 Используйте необязательный индикатор Z часового пояса, чтобы упростить сопоставление значений даты и времени XML, имеющих сведения о часовом поясе, с SQL Server значениями даты и времени без часового пояса. Z указывает часовой пояс в формате UTC-0. Смещение HH:MM в + направлении или - указывает на другие часовые пояса. Например: 2022-12-12T23:45:12-08:00.
При преобразовании в символьные данные из smalldatetime стили, включающие секунды или миллисекунды, будут содержать нули в соответствующих позициях. При преобразовании из datetime или smalldatetime ненужные части даты можно усекать с помощью типа данных char или varchar соответствующей длины.
При преобразовании в тип данных datetimeoffset из символьных данных со стилем, включающим время, смещение часового пояса добавляется к результату.
Стили данных float и real
Если аргумент expression принадлежит к типу данных float или real, аргумент style может иметь одно из значений, приведенных в таблице ниже. Другие значения обрабатываются как 0.
| Значение | Выходные данные |
|---|---|
| 0 (по умолчанию) | Не более 6 разрядов. По необходимости используется экспоненциальное представление чисел. |
| 1 | Всегда 8 разрядов. Всегда используется экспоненциальное представление чисел. |
| 2 | Всегда 16 разрядов. Всегда используется экспоненциальное представление чисел. |
| 3 | Всегда 17 разрядов. Используется для преобразования без потери данных. При использовании этого стиля каждое отдельное значение типа float или real гарантированно преобразуется в отдельную строку символов. Применимо к: SQL Server 2016 (13.x) и более поздних версий, а также база данных Azure SQL. |
| 126, 128, 129 | Включено по причинам прежних версий. Не используйте эти значения для новой разработки. |
Стили данных money и smallmoney
Если аргумент expression принадлежит к типу данных money или smallmoney, аргумент style может иметь одно из значений, приведенных в таблице ниже. Другие значения обрабатываются как 0.
| Значение | Выходные данные |
|---|---|
| 0 (по умолчанию) | Без запятых, разделяющих группы разрядов, с двумя цифрами справа от десятичного разделителя. Пример 4235.98. |
| 1 | Запятые, разделяющие группы из трех разрядов слева от десятичного разделителя, с двумя цифрами справа от десятичного разделителя. Пример 3,510.92. |
| 2 | Без запятых, разделяющих группы разрядов, с четырьмя цифрами справа от десятичного разделителя. Пример 4235.9819. |
| 126 | Эквивалент стиля 2 при преобразовании в char(n) или varchar(n) |
Стили данных XML
Если аргумент expression принадлежит к типу данных xml, аргумент style может иметь одно из значений, приведенных в таблице ниже. Другие значения обрабатываются как 0.
| Значение | Выходные данные |
|---|---|
| 0 (по умолчанию) | Используйте поведение синтаксического анализа по умолчанию, которое удаляет незначительные пробелы и не допускает внутреннего подмножества DTD. Примечание. При преобразовании в тип данных xmlSQL Server обрабатывает незначащие пробелы не так, как это описано в стандарте XML 1.0. Дополнительные сведения см. в статье Создание экземпляров XML-данных. |
| 1 | Сохранять незначащие пробельные символы. Этот параметр стиля задает обработку по умолчанию xml:space в соответствии с поведением xml:space="preserve". |
| 2 | Использовать ограниченную обработку внутреннего подмножества DTD. При этом для выполнения операций синтаксического анализа без проверки действительности сервер может пользоваться следующей информацией, предоставляемой внутренним подмножеством DTD. — Применяются атрибуты по умолчанию. — Ссылки на внутренние сущности разрешаются и раскрываются. — Проверяется синтаксическая правильность модели содержимого DTD. Синтаксический анализатор пропускает внешние подмножества DTD. Кроме того, он не оценивает XML-объявление, чтобы определить, имеет ли автономный атрибут значение "да " или "нет ". Вместо этого выполняется анализ экземпляра XML как отдельного документа. |
| 3 | Сохранять незначащие пробельные символы и использовать ограниченную обработку внутреннего подмножества DTD. |
Стили двоичных данных
Для выражения binary(n), char(n), varbinary(n) или varchar(n)выражениеstyle может иметь одно из значений, показанных в следующей таблице. При использовании значений стиля, отсутствующих в этой таблице, возвращается ошибка.
| Значение | Выходные данные |
|---|---|
| 0 (по умолчанию) | Преобразует символы ASCII в двоичные байты либо двоичные байты в символы ASCII. Каждый символ или байт преобразуется в соотношении 1:1. Если параметр data_type имеет значение binary, к результату слева добавляются символы 0x. |
| 1, 2 | Если параметр data_type имеет значение binary, выражение должно быть символьным. Значение аргумента expression должно состоять из четного числа шестнадцатеричных знаков (0, 1, 2, 3, 4, 5, 6, 7, 8, 9, A, B, C, D, E, F, a, b, c, d, e, f). Если аргумент style имеет значение 1, в качестве первых двух символов выражения обязательно использовать 0x. Если выражение содержит нечетное число символов или использованы недопустимые символы, возникает ошибка. Если длина преобразованного выражения превышает длину типа данных data_type, результат усекается справа. При использовании значений аргумента data_type фиксированной длины, превышающей длину преобразованного результата, к результату справа добавляются нули. Аргумент data_type символьного типа требует двоичного выражения. Каждый двоичный символ преобразуется в два шестнадцатеричных символа. Предположим, длина преобразованного выражения превышает длину типа данных data_type. В этом случае оно усекается. Если для параметра data_type используется значение символьного типа фиксированного размера и длина преобразованного результата меньше длины типа данных data_type, к преобразованному выражению справа добавляются символы пробела, чтобы сохранить четность числа шестнадцатеричных знаков. Символы 0x не добавляются слева от преобразованного результата для стиля 2. |
Неявные преобразования
Для неявных преобразований не требуется спецификация CAST функции или CONVERT функции. Для явных преобразований требуется спецификация CAST функции или CONVERT функции . На следующей иллюстрации показаны все явные и неявные преобразования типов данных, допустимые для системных типов данных SQL Server. Это могут быть типы bigint, sql_variant и xml. При присваивании неявного преобразования из типа sql_variant не происходит, но неявное преобразование в тип sql_variant производится.
Совет
В Центре загрузки Майкрософт эта диаграмма представлена в виде PNG-файла, который можно скачать.
На приведенной выше диаграмме показаны все явные и неявные преобразования, которые допускаются в SQL Server, однако результирующий тип данных преобразования зависит от выполняемой операции.
- При явных преобразованиях сам оператор определяет результирующий тип данных.
- При неявных преобразованиях операторы назначения, такие как установка значения переменной или вставка значения в столбец, дают в результате тип данных, определенный в объявлении переменной или в определении столбца.
- Для операторов сравнения или других выражений результирующий тип данных зависит от правил приоритета типов данных.
Совет
Практический пример применения приоритета типов данных при преобразованиях приводится далее в этом разделе.
При преобразовании между datetimeoffset и символьными типами char, nchar, nvarchar и varchar преобразованная часть смещения часового пояса всегда должна иметь двойные цифры для и HHMM. Например, -08:00.
Так как у данных в Юникоде всегда четное число байтов, будьте осторожны при преобразовании значений типа binary или varbinary в типы данных, поддерживающие Юникод, и наоборот. Например, следующее преобразование не возвращает шестнадцатеричное значение 41. Он возвращает шестнадцатеричное значение 4100:
SELECT CAST(CAST(0x41 AS nvarchar) AS varbinary);
Дополнительные сведения см. в статье Collation and Unicode Support.
Типы данных больших значений
Типы данных большого объема демонстрируют то же поведение при явных и неявных преобразованиях, что и их аналоги меньшего объема, а именно типы данных nvarchar, varbinary и varchar. Тем не менее необходимо учитывать следующие правила:
- Преобразование из image в varbinary(max) и обратно неявное, как и преобразования между text и varchar(max) , а также ntext и nvarchar(max) .
- Преобразование из типов данных большого объема, например varchar(max) , в аналогичный тип данных меньшего объема, например varchar, неявное, но если объем данных слишком велик, будет произведено усечение данных до указанной длины конкретного типа данных меньшего объема.
- Преобразование из nvarchar, varbinary или varchar в соответствующие им типы данных большого объема выполняется неявно.
- Преобразование из типа данных sql_variant в типы данных большого объема выполняется явно.
- Типы данных с большими значениями нельзя преобразовать в тип данных sql_variant .
Дополнительные сведения о преобразовании из типа данных xml см. в разделе Создание экземпляров XML-данных.
Тип данных XML
При явном или неявном приведении типа данных xml к строковому или двоичному типу данных содержимое типа данных xml сериализуется согласно набору определенных правил. Сведения об этих правилах см. в разделе Определение сериализации XML-данных. Дополнительные сведения о преобразовании других типов данных в тип данных xml см. в разделе Создание экземпляров XML-данных.
Типы данных text и image
Типы данных text и image не поддерживают автоматическое преобразование типов данных. Можно явно преобразовать text в символьные данные, а image — в binary или varbinary, но длиной не более 8000 байт. Если вы попробуете произвести неверное преобразование, например преобразовать символьное выражение, содержащее буквы, в int, SQL Server вернет ошибку.
Параметры сортировки выходных данных
CAST Когда функции или CONVERT выводят символьную строку и получают входные данные символьной строки, выходные данные имеют те же параметры сортировки и метки параметров сортировки, что и входные данные. Если входные данные не является символьной строкой, выходные данные имеют параметры сортировки по умолчанию для базы данных и метку параметров сортировки coercible-default. Дополнительные сведения см. в разделе Очередность параметров сортировки (Transact-SQL).
Чтобы назначить выходным данным другие параметры сортировки, примените предложение COLLATE к результирующем выражению CAST функции или CONVERT . Пример:
SELECT CAST('abc' AS varchar(5)) COLLATE French_CS_AS;
Усечение и округление результатов
При преобразовании символьных или двоичных выражений (binary, char, nchar, nvarchar, varbinary или varchar) в выражение другого типа данных операция преобразования может усекать выходные данные, отображать их лишь частично или возвращать ошибку. Это происходит в тех случаях, когда результат имеет слишком малую длину для отображения. Результаты преобразований в binary, char, nchar, nvarchar, varbinary или varchar усекаются всегда, за исключением случаев, перечисленных в таблице ниже.
| Из типа данных | В тип данных | Результат |
|---|---|---|
| int, smallint или tinyint | char varchar |
Слишком короткий для отображения |
| nchar nvarchar |
Ошибка 1 | |
| money, smallmoney, numeric, decimal, float или real | char varchar |
Ошибка 1 |
| nchar nvarchar |
Ошибка 1 |
1 Возвращена ошибка, так как длина результата слишком коротка для отображения.
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));
GO
Предупреждение
Не создавайте двоичные значения, а затем преобразуйте их в тип данных категории числового типа данных. SQL Server не гарантирует, что результат преобразования типа данных decimal или numeric в binary будет одинаковым в разных версиях SQL Server.
В следующем примере показано результирующее выражение, которое слишком мало для отображения:
USE AdventureWorks2019;
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
INNER JOIN Person.Person p
ON e.BusinessEntityID = p.BusinessEntityID
WHERE NOT e.BusinessEntityID > 5;
GO
Результирующий набор:
FirstName LastName Title Sick Leave
--------- ------------- ------- --------`
Ken Sanchez NULL *
Terri Duffy NULL *
Roberto Tamburello NULL *
Rob Walters NULL *
Gail Erickson Ms. *
(5 row(s) affected)
При преобразовании между типами данных с разными длинами дробных частей SQL Server может усекать или округлять результат. В следующей таблице описано это поведение.
| От | Чтобы | Поведение |
|---|---|---|
| numeric | numeric | Round |
| numeric | int | Truncate |
| numeric | money | Round |
| money | int | Round |
| money | numeric | Round |
| float | int | Truncate |
| float | numeric | Раунд 1 |
| float | datetime | Round |
| datetime | int | Round |
1 Преобразование значений с плавающей точкой , использующих научное нотирование, в десятичное или числовое значение ограничивается только значениями точности 17 цифр. Любое значение с точностью, превышающей 17 знаков, округляется до нуля.
Например, значения 10,6496 и –10,6496 могут усекаться или округляться при преобразовании в тип int или numeric:
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;
Результаты запроса показаны в приведенной ниже таблице.
| trunc1 | trunc2 | round1 | round2 |
|---|---|---|---|
| 10 | –10 | 11 | -11 |
При преобразовании к типам данных, у которых дробная часть короче, чем у исходного типа, значение округляется. Например, это преобразование возвращает $10.3497:
SELECT CAST(10.3496847 AS money);
SQL Server возвращает сообщение об ошибке при попытке преобразовать нечисловые данные типа char, nchar, nvarchar или varchar в тип decimal, float, int или numeric. SQL Server также возвращает сообщение об ошибке при попытке преобразования пустой строки (" ") в тип numeric или decimal.
Некоторые преобразования типа данных даты и времени являются недетерминированными
В следующих стилях преобразование строк в тип datetime недетерминировано:
- Все стили ниже 100 1
- 106
- 107
- 109
- 113
- 130
1 За исключением стилей 20 и 21.
Дополнительные сведения см. в статье Недетерминированное преобразование строк дат литералов в значения DATE.
Дополнительные символы (суррогатные пары)
Начиная с SQL Server 2012 (11.x) при использовании параметров сортировки дополнительных символов CAST (SC) операция из nchar или nvarchar в тип nchar или nvarchar меньшей длины не усекается внутри суррогатной пары. Вместо этого усечение происходит перед дополнительным символом. Например, выполнение следующего фрагмента кода приведет к тому, что в @x останется лишь 'ab'. Недостаточно места для размещения дополнительного символа.
DECLARE @x NVARCHAR(10) = 'ab' + NCHAR(0x10000);
SELECT CAST(@x AS NVARCHAR(3));
При использовании параметров сортировки SC поведение CONVERT аналогично CAST. Дополнительные сведения см. в статье Поддержка параметров сортировки и Юникода — дополнительные символы.
Поддержка совместимости
В более ранних версиях SQL Server стиль по умолчанию для CAST операций с CONVERT типами данных time и datetime2 — 121, за исключением случаев, когда в выражении вычисляемого столбца используется любой из типов. Для вычисляемых столбцов используемый по умолчанию стиль — 0. Это поведение влияет на вычисляемые столбцы при их создании и использовании в запросах с автоматической параметризацией, а также при использовании в определениях ограничений.
При уровне совместимости 110 и выше CAST операции и CONVERT для типов данных time и datetime2 всегда имеют 121 в качестве стиля по умолчанию. Если запрос основан на прежнем поведении, следует использовать уровень совместимости ниже 110 либо явно задать в затрагиваемом запросе стиль 0.
| Значение уровня совместимости | Стиль по умолчанию для CAST и CONVERT1 |
Стиль по умолчанию для вычисляемого столбца |
|---|---|---|
| <110 | 121 | 0 |
| > = 110 | 121 | 121 |
1 Кроме вычисляемых столбцов
Обновление базы данных до уровня совместимости 110 и выше не приведет к изменению пользовательских данных, сохраненных на диске. Следует исправить эти данных соответствующим образом вручную. Например, если бы вы использовали предложение SELECT INTO для создания таблицы на основе источника, содержащего описанное выше выражение вычисляемого столбца, то сохранялись бы данные (благодаря стилю 0), а не само определение вычисляемого столбца. В таком случае необходимо вручную обновлять эти данные в соответствии со стилем 121.
Примеры
A. Используйте как CAST, так и CONVERT
В этих примерах извлекается имя продукта для тех продуктов, которые имеют в 3 качестве первой цифры цены по списку, и их значения преобразуются ListPriceв int.
Используйте CAST в следующих случаях:
USE AdventureWorks2019;
GO
SELECT SUBSTRING(Name, 1, 30) AS ProductName,
ListPrice
FROM Production.Product
WHERE CAST(ListPrice AS INT) LIKE '33%';
GO
Используйте CONVERT в следующих случаях:
USE AdventureWorks2019;
GO
SELECT SUBSTRING(Name, 1, 30) AS ProductName,
ListPrice
FROM Production.Product
WHERE CONVERT(INT, ListPrice) LIKE '33%';
GO
Результирующий набор: Выборка результирующих наборов одинакова как для , так CAST и CONVERTдля .
ProductName ListPrice
------------------------------ ---------------------
LL Road Frame - Black, 58 337.22
LL Road Frame - Black, 60 337.22
LL Road Frame - Black, 62 337.22
LL Road Frame - Red, 44 337.22
LL Road Frame - Red, 48 337.22
LL Road Frame - Red, 52 337.22
LL Road Frame - Red, 58 337.22
LL Road Frame - Red, 60 337.22
LL Road Frame - Red, 62 337.22
LL Road Frame - Black, 44 337.22
LL Road Frame - Black, 48 337.22
LL Road Frame - Black, 52 337.22
Mountain-100 Black, 38 3374.99
Mountain-100 Black, 42 3374.99
Mountain-100 Black, 44 3374.99
Mountain-100 Black, 48 3374.99
HL Road Front Wheel 330.06
LL Touring Frame - Yellow, 62 333.42
LL Touring Frame - Blue, 50 333.42
LL Touring Frame - Blue, 54 333.42
LL Touring Frame - Blue, 58 333.42
LL Touring Frame - Blue, 62 333.42
LL Touring Frame - Yellow, 44 333.42
LL Touring Frame - Yellow, 50 333.42
LL Touring Frame - Yellow, 54 333.42
LL Touring Frame - Yellow, 58 333.42
LL Touring Frame - Blue, 44 333.42
HL Road Tire 32.60
(28 rows affected)
Б. Использование CAST с арифметическими операторами
В этом примере вычисляется столбец значений (Computed) путем деления суммарных продаж за год (SalesYTD) на проценты комиссионных (CommissionPCT). Это значение округляется до ближайшего целого числа, а затем CAST — до типа данных int .
USE AdventureWorks2019;
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 для объединения
В этом примере сцепляется несимвые выражения с помощью CAST. В нем используется база данных AdventureWorksDW2019.
SELECT 'The list price is ' + CAST(ListPrice AS VARCHAR(12)) AS ListPrice
FROM dbo.DimProduct
WHERE ListPrice BETWEEN 350.00 AND 400.00;
Результирующий набор:
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
Г. Использование CAST для создания более удобочитаемого текста
В этом примере используется CAST в списке SELECT для преобразования столбца Name в столбец char(10). В нем используется база данных AdventureWorksDW2019.
SELECT DISTINCT CAST(EnglishProductName AS CHAR(10)) AS Name,
ListPrice
FROM dbo.DimProduct
WHERE EnglishProductName LIKE 'Long-Sleeve Logo Jersey, M';
GO
Результирующий набор:
Name ListPrice
---------- ---------
Long-Sleev 31.2437
Long-Sleev 32.4935
Long-Sleev 49.99
Д. Использование CAST с предложением LIKE
В этом примере значения столбцов SalesYTD преобразуются money в тип данных int, а затем в тип данных char(20), чтобы его можно было использовать в предложении LIKE .
USE AdventureWorks2019;
GO
SELECT p.FirstName,
p.LastName,
s.SalesYTD,
s.BusinessEntityID
FROM Person.Person AS p
INNER JOIN Sales.SalesPerson AS s
ON p.BusinessEntityID = s.BusinessEntityID
WHERE CAST(CAST(s.SalesYTD AS INT) AS CHAR(20)) LIKE '2%';
GO
Результирующий набор:
FirstName LastName SalesYTD BusinessEntityID
---------------- ------------------- ---------------- -------------
Tsvi Reiter 2811012.7151 279
Syed Abbas 219088.8836 288
Rachel Valdez 2241204.0424 289
(3 row(s) affected)
Е. Использование CONVERT или CAST с типизированным XML
В этих примерах показано использование CONVERT для преобразования данных в типизированный XML с помощью типов данных и столбцов XML (SQL Server).
В этом примере строка, содержащая пробельные символы, текст и разметку, преобразуется в типизированный XML, в котором удаляются все незначащие пробельные символы (пробелы, разделяющие узлы):
SELECT CONVERT(XML, '<root><child/></root>')
В этом примере похожая строка, содержащая пробельные символы, текст и разметку, преобразуется в типизированный XML, в котором сохраняются все незначащие пробельные символы (пробелы, разделяющие узлы):
SELECT CONVERT(XML, '<root> <child/> </root>', 1)
В этом примере строка, содержащая пробельные символы, текст и разметку, приводится к типизированному XML:
SELECT CAST('<Name><FName>Carol</FName><LName>Elliot</LName></Name>' AS XML)
Дополнительные примеры см. в статье Создание экземпляров XML-данных.
Ж. Использование CAST и CONVERT с данными даты и времени
Начиная со значений GETDATE() этот пример показывает текущие дату и время, использует функцию CAST для изменения текущей даты и времени в символьный тип данных и затем использует CONVERT для отображения даты и времени в формате ISO 8601.
SELECT GETDATE() AS UnconvertedDateTime,
CAST(GETDATE() AS NVARCHAR(30)) AS UsingCast,
CONVERT(NVARCHAR(30), GETDATE(), 126) AS UsingConvertTo_ISO8601;
GO
Результирующий набор:
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)
Этот пример — частичная противоположность предыдущему примеру. В этом примере в качестве символьных данных отображаются дата и время, используется 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 с двоичными и символьными данными
В этих примерах показаны результаты преобразования двоичных и символьных данных с использованием различных стилей.
--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)
В этом примере показано, что стиль 1 может привести к принудительному усечению результата. К этому приводит наличие символов 0x в результате.
SELECT CONVERT(CHAR(8), 0x4E616d65, 1) AS [Style 1, binary to character];
Результирующий набор:
Style 1, binary to character
------------------------------
0x4E616D
(1 row(s) affected)
В этом примере показано, что стиль 2 не усекает результат, так как результат не содержит символов 0x.
SELECT CONVERT(CHAR(8), 0x4E616d65, 2) AS [Style 2, binary to character];
Результирующий набор:
Style 2, binary to character
------------------------------
4E616D65
(1 row(s) affected)
Преобразуйте символьное значение "Name" в двоичное значение.
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)
И. Преобразование типов данных даты и времени
В этом примере показано преобразование типов данных date, time и datetime .
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];
При преобразовании даты в datetime или datetime2 убедитесь, что значения находятся в пределах совместимого диапазона. Минимальное значение года для datetime — 1753, а минимальное значение года — 0001 для date и datetime2.
DECLARE @d1 DATE, @dt1 DATETIME , @dt2 DATETIME2
SET @d1 = '1492-08-03'
--This is okay; Minimum YYYY for DATE is 0001
SET @dt2 = CAST(@d1 AS DATETIME2)
--This is okay; Minimum YYYY for DATETIME2 IS 0001
SET @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
К. Использование функции CONVERT с данными даты и времени в разных форматах
Начиная со значений GETDATE() этот пример использует функцию CONVERT для отображения всех стилей даты и времени в разделе Стили даты и времени этой статьи.
| Номер формата | Пример запроса | Пример результата |
|---|---|---|
| 0 | SELECT CONVERT(NVARCHAR, GETDATE(), 0) |
23 авг 2019 13:39 |
| 1 | SELECT CONVERT(NVARCHAR, GETDATE(), 1) |
08/23/19 |
| 2 | SELECT CONVERT(NVARCHAR, GETDATE(), 2) |
19.08.23 |
| 3 | SELECT CONVERT(NVARCHAR, GETDATE(), 3) |
23/08/19 |
| 4 | SELECT CONVERT(NVARCHAR, GETDATE(), 4) |
23.08.19 |
| 5 | SELECT CONVERT(NVARCHAR, GETDATE(), 5) |
23-08-19 |
| 6 | SELECT CONVERT(NVARCHAR, GETDATE(), 6) |
23 авг 19 |
| 7 | SELECT CONVERT(NVARCHAR, GETDATE(), 7) |
Авг 23, 19 |
| 8 или 24 или 108 | SELECT CONVERT(NVARCHAR, GETDATE(), 8) |
13:39:17 |
| 9 или 109 | SELECT CONVERT(NVARCHAR, GETDATE(), 9) |
23 авг 2019 13:39:17:090 |
| 10 | SELECT CONVERT(NVARCHAR, GETDATE(), 10) |
08-23-19 |
| 11 | SELECT CONVERT(NVARCHAR, GETDATE(), 11) |
19/08/23 |
| 12 | SELECT CONVERT(NVARCHAR, GETDATE(), 12) |
190823 |
| 13 или 113 | SELECT CONVERT(NVARCHAR, GETDATE(), 13) |
23 авг 2019 13:39:17:090 |
| 14 или 114 | SELECT CONVERT(NVARCHAR, GETDATE(), 14) |
13:39:17:090 |
| 20 или 120 | SELECT CONVERT(NVARCHAR, GETDATE(), 20) |
2019-08-23 13:39:17 |
| 21 или 25 или 121 | SELECT CONVERT(NVARCHAR, GETDATE(), 21) |
2019-08-23 13:39:17.090 |
| 22 | SELECT CONVERT(NVARCHAR, GETDATE(), 22) |
23/08/19 13:39:17 |
| 23 | SELECT CONVERT(NVARCHAR, GETDATE(), 23) |
2019-08-23 |
| 101 | SELECT CONVERT(NVARCHAR, GETDATE(), 101) |
08/23/2019 |
| 102 | SELECT CONVERT(NVARCHAR, GETDATE(), 102) |
2019.08.23 |
| 103 | SELECT CONVERT(NVARCHAR, GETDATE(), 103) |
23/08/2019 |
| 104 | SELECT CONVERT(NVARCHAR, GETDATE(), 104) |
23.08.2019 |
| 105 | SELECT CONVERT(NVARCHAR, GETDATE(), 105) |
23-08-2019 |
| 106 | SELECT CONVERT(NVARCHAR, GETDATE(), 106) |
23 авг 2019 |
| 107 | SELECT CONVERT(NVARCHAR, GETDATE(), 107) |
Авг 23, 2019 |
| 110 | SELECT CONVERT(NVARCHAR, GETDATE(), 110) |
08-23-2019 |
| 111 | SELECT CONVERT(NVARCHAR, GETDATE(), 111) |
2019/08/23 |
| 112 | SELECT CONVERT(NVARCHAR, GETDATE(), 112) |
20190823 |
| 113 | SELECT CONVERT(NVARCHAR, GETDATE(), 113) |
23 авг 2019 13:39:17.090 |
| 120 | SELECT CONVERT(NVARCHAR, GETDATE(), 120) |
2019-08-23 13:39:17 |
| 121 | SELECT CONVERT(NVARCHAR, GETDATE(), 121) |
2019-08-23 13:39:17.090 |
| 126 | SELECT CONVERT(NVARCHAR, GETDATE(), 126) |
2019-08-23T13:39:17.09 |
| 127 | SELECT CONVERT(NVARCHAR, GETDATE(), 127) |
2019-08-23T13:39:17.09 |
| 130 | SELECT CONVERT(NVARCHAR, GETDATE(), 130) |
22 ذو الحجة 1440 1:39:17.090P |
| 131 | SELECT CONVERT(NVARCHAR, GETDATE(), 131) |
22/12/1440 13:39:17.090 |
Л. Результат применения приоритета типов данных при допустимых преобразованиях
В следующем примере определяется переменная типа varchar(10), присваивается целочисленное значение переменной, а затем выбирается объединение переменной со строкой.
DECLARE @string VARCHAR(10);
SET @string = 1;
SELECT @string + ' is a string.' AS Result
Результирующий набор:
Result
-----------------------
1 is a string.
Значение int , равное 1, было преобразовано в varchar.
В этом примере показан аналогичный запрос с использованием переменной int :
DECLARE @notastring INT;
SET @notastring = '1';
SELECT @notastring + ' is not a string.' AS Result
В этом случае инструкция SELECT выдает следующую ошибку:
Msg 245, Level 16, State 1, Line 3
Conversion failed when converting the varchar value ' is not a string.' to data type int.
Чтобы вычислить выражение @notastring + ' is not a string.', SQL Server необходимо следовать правилам приоритета типов данных для выполнения неявного преобразования перед вычислением результата выражения. Так как значение int имеет более высокий приоритет, чем varchar, SQL Server пытается преобразовать строку в целое число и завершается сбоем, так как эту строку нельзя преобразовать в целое число.
Если предоставить строку, которую можно преобразовать, инструкция завершается успешно, как показано в следующем примере:
DECLARE @notastring INT;
SET @notastring = '1';
SELECT @notastring + '1'
В этом случае строка '1' может быть преобразована в целочисленное значение 1, поэтому инструкция SELECT возвращает значение 2. Если предоставленные типы данных являются целочисленными, оператор + становится математическим оператором сложения, а не объединения строк.
Примеры: Azure Synapse Analytics и Система платформы аналитики (PDW)
М. Использование CAST и CONVERT
В этом примере извлекаются имена продуктов, у которых первая цифра цены — 3, а затем их значения ListPrice преобразовываются в int. В нем используется база данных AdventureWorksDW2019.
SELECT EnglishProductName AS ProductName, ListPrice
FROM dbo.DimProduct
WHERE CAST(ListPrice AS int) LIKE '3%';
В этом примере показан тот же запрос с использованием CONVERT вместо CAST. В нем используется база данных AdventureWorksDW2019.
SELECT EnglishProductName AS ProductName, ListPrice
FROM dbo.DimProduct
WHERE CONVERT(INT, ListPrice) LIKE '3%';
Н. Использование CAST с арифметическими операторами
В этом примере вычисляется отдельное значение столбца путем деления цены единицы товара (UnitPrice) на процент скидки (UnitPriceDiscountPct). Затем этот результат округляется до ближайшего целого числа и, наконец, преобразуется в тип данных int . В этом примере используется база данных AdventureWorksDW2019.
SELECT ProductKey, UnitPrice,UnitPriceDiscountPct,
CAST(ROUND (UnitPrice*UnitPriceDiscountPct,0) AS int) AS DiscountPrice
FROM dbo.FactResellerSales
WHERE SalesOrderNumber = 'SO47355'
AND UnitPriceDiscountPct > .02;
Результирующий набор:
ProductKey UnitPrice UnitPriceDiscountPct DiscountPrice
---------- --------- -------------------- -------------
323 430.6445 0.05 22
213 18.5043 0.05 1
456 37.4950 0.10 4
456 37.4950 0.10 4
216 18.5043 0.05 1
О. Использование CAST с предложением LIKE
В этом примере столбец ListPrice типа money преобразуется в тип int, а затем в тип char(20) так, чтобы его можно было использовать в предложении LIKE. В этом примере используется база данных AdventureWorksDW2019.
SELECT EnglishProductName AS Name, ListPrice
FROM dbo.DimProduct
WHERE CAST(CAST(ListPrice AS INT) AS CHAR(20)) LIKE '2%';
П. Использование CAST и CONVERT с данными даты и времени
В этом примере отображаются текущие дата и время, используется CAST для изменения текущей даты и времени на символьный тип данных и, наконец, используется CONVERT отображение даты и времени в формате ISO 8601. В этом примере используется база данных AdventureWorksDW2019.
SELECT TOP(1)
SYSDATETIME() AS UnconvertedDateTime,
CAST(SYSDATETIME() AS NVARCHAR(30)) AS UsingCast,
CONVERT(NVARCHAR(30), SYSDATETIME(), 126) AS UsingConvertTo_ISO8601
FROM dbo.DimCustomer;
Результирующий набор:
UnconvertedDateTime UsingCast UsingConvertTo_ISO8601
--------------------- --------------------------- ---------------------------
07/20/2010 1:44:31 PM 2010-07-20 13:44:31.5879025 2010-07-20T13:44:31.5879025
Этот пример — частичная противоположность предыдущему примеру. В этом примере в качестве символьных данных отображается дата и время, используется CAST для изменения символьных данных на тип данных datetime , а затем используется CONVERT для изменения символьных данных на тип данных datetime . В этом примере используется база данных AdventureWorksDW2019.
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_ISO8601
FROM dbo.DimCustomer;
Результирующий набор:
UnconvertedText UsingCast UsingConvertFrom_ISO8601
----------------------- ----------------------- ------------------------
2010-07-25T13:50:38.544 07/25/2010 1:50:38 PM 07/25/2010 1:50:38 PM
См. также раздел
- FORMAT (Transact-SQL)
- STR (Transact-SQL)
- SELECT (Transact-SQL)
- Системные функции (Transact-SQL)
- Поддержка параметров сортировки и Юникода