Функции CAST и CONVERT (Transact-SQL)
Область применения:
SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure конечной точке аналитики платформы Аналитики Azure Synapse Analytics (PDW) в Microsoft Fabric Хранилище в Microsoft Fabric
Эти функции преобразуют выражение одного типа данных в другой.
Синтаксис
CAST синтаксис:
CAST ( expression AS data_type [ ( length ) ] )
CONVERT синтаксис:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
Соглашения о синтаксисе Transact-SQL
Аргументы
выражение
Любое допустимое выражение.
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 | С веком (гггг) | Стандартные | Входные и выходные данные 3 |
|---|---|---|---|
| - | 0 или 100 1, 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 | 106 1 | - | 6 = dd mon yy106 = dd mon yyyy |
| 7 | 107 1 | - | 7 = Mon dd, yy107 = Mon dd, yyyy |
| 8 или 24 | 108 | - | hh:mi:ss |
| - | 9 или 109 1, 2 | По умолчанию + миллисекунды | mon dd yyyy hh:mi:ss:mmmAM (или PM) |
| 10 | 110 | USA | 10 = мм-дд-гг 110 = mm-dd-yyyy |
| 11 | 111 | Япония | 11 = гг/мм/дд 111 = yyyy/mm/dd |
| 12 | 112 | ISO | 12 = ггммдд 112 = yyyymmdd |
| - | 13 или 113 1, 2 | Европейский по умолчанию + миллисекунды | dd mon yyyy hh:mi:ss:mmm (24-час) |
| 14 | 114 | - | hh:mi:ss:mmm (24-час) |
| - | 20 или 120 2 | Канонический формат ODBC | yyyy-mm-dd hh:mi:ss (24-час) |
| - | 21 или 25 или 121 2 | Канонический ODBC (с миллисекундами) по умолчанию для времени, даты, даты, даты и времени и datetimeoffset | yyyy-mm-dd hh:mi:ss.mmm (24-час) |
| 22 | - | США | mm/dd/yy hh:mi:ss AM (или PM) |
| - | 23 | ISO8601 | yyyy-mm-dd |
| - | 126 4 | ISO8601 | yyyy-mm-ddThh:mi:ss.mmm (без пробелов) 6 |
| - | 127 8, 9 | ISO8601 с часовыми поясами Z | yyyy-MM-ddThh:mm:ss.fffZ (без пробелов) 6 |
| - | 130 1, 2 | Хиджри 5 | dd mon yyyy hh:mi:ss:mmmAM7 |
| - | 131 2 | Хиджри 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 поддерживается только при приведение символьных данных к дате или 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) и более поздних версий и База данных SQL Azure. |
| 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. Примечание. При преобразовании в тип данных XML sql 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) стиль может иметь одно из значений, показанных в следующей таблице. При использовании значений стиля, отсутствующих в этой таблице, возвращается ошибка.
| Значение | Выходные данные |
|---|---|
| 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 преобразованная часть смещения часового пояса всегда должна иметь двойные цифры для обоих HH иMM. Например, -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 — в binary или varbinary, но длиной не более 8000 байт. При попытке неправильного преобразования, например при попытке преобразовать символьное выражение, включающее буквы в int, SQL Server возвращает сообщение об ошибке.
Параметры сортировки выходных данных
CAST CONVERT Когда или функции выводят символьную строку и получают входные данные строки символов, выходные данные имеют ту же метку сортировки и сортировки, что и входные данные. Если входные данные не является строкой символов, выходные данные имеют параметры сортировки по умолчанию базы данных и метку сортировки для принудительного использования по умолчанию. Дополнительные сведения см. в разделе Очередность параметров сортировки (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 не гарантирует, что результат преобразования десятичного или числового типа данных в двоичный файл будет одинаковым между версиями SQL Server.
В следующем примере показано результирующее выражение, которое слишком мало для отображения:
USE AdventureWorks2022;
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 возвращает сообщение об ошибке при преобразовании ненумерных символов, nchar, nvarchar или varchar данных в десятичные, float, int, числовые. SQL Server также возвращает ошибку при преобразовании пустой строки ("") в числовой или десятичный.
некоторые преобразования типа данных даты и времени являются недетерминированными
В следующих стилях преобразование строк в тип 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 типов данных time и CONVERT datetime2 составляет 121, за исключением случаев, когда любой тип используется в вычисляемом выражении столбца. Для вычисляемых столбцов используемый по умолчанию стиль — 0. Это поведение влияет на вычисляемые столбцы при их создании и использовании в запросах с автоматической параметризацией, а также при использовании в определениях ограничений.
При уровне совместимости 110 и более поздних CONVERT CAST версий и операциях с типами данных datetime2 всегда есть 121 в качестве стиля по умолчанию. Если запрос основан на прежнем поведении, следует использовать уровень совместимости ниже 110 либо явно задать в затрагиваемом запросе стиль 0.
| Значение уровня совместимости | Стиль по умолчанию для CAST и CONVERT 1 |
Стиль по умолчанию для вычисляемого столбца |
|---|---|---|
| <110 | 121 | 0 |
| > = 110 | 121 | 121 |
1 Кроме вычисляемых столбцов
Обновление базы данных до уровня совместимости 110 и более поздних версий не изменит пользовательские данные, хранящиеся на диске. Следует исправить эти данных соответствующим образом вручную. Например, если бы вы использовали предложение SELECT INTO для создания таблицы на основе источника, содержащего описанное выше выражение вычисляемого столбца, то сохранялись бы данные (благодаря стилю 0), а не само определение вычисляемого столбца. В таком случае необходимо вручную обновлять эти данные в соответствии со стилем 121.
Примеры
А. Использование CAST и CONVERT
Эти примеры извлекают имя продукта, для тех продуктов, которые имеют 3 первую цифру цены на список, и преобразует их ListPrice значения в int.
Используйте CAST в следующих случаях:
USE AdventureWorks2022;
GO
SELECT SUBSTRING(Name, 1, 30) AS ProductName,
ListPrice
FROM Production.Product
WHERE CAST(ListPrice AS INT) LIKE '33%';
GO
Используйте CONVERT в следующих случаях:
USE AdventureWorks2022;
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)
B. Использование CAST с арифметическими операторами
В этом примере вычисляется столбец значений (Computed) путем деления суммарных продаж за год (SalesYTD) на проценты комиссионных (CommissionPCT). Это значение округляется до ближайшего целого числа, а затем CAST — до типа данных int.
USE AdventureWorks2022;
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. В нем используется база данных AdventureWorksDW2022.
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
D. Использование CAST для создания более удобочитаемого текста
Этот пример используется CAST в списке SELECT для преобразования столбца Name в столбец char(10). В нем используется база данных AdventureWorksDW2022.
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 AdventureWorks2022;
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)
F. Использование 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-данных.
G. Использование CAST и CONVERT с данными datetime
Начиная со значений 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)
H. Использование 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)
I. Преобразование типов данных даты и времени
В этом примере показано преобразование типов данных даты, времени и даты и времени .
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];
Убедитесь, что значения находятся в совместимом диапазоне при рассмотрении преобразования с даты на дату или datetime2. Минимальное значение года для даты и времени1753, а минимальное значение года — 0001 для даты и даты и времени 2.
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
J. Использование CONVERT с данными datetime в различных форматах
Начиная со значений 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.090 |
| 127 | SELECT CONVERT(NVARCHAR, GETDATE(), 127) |
2019-08-23T13:39:17.090 |
| 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 |
K. Результат применения приоритета типов данных при допустимых преобразованиях
В следующем примере определяется переменная типа 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)
L. Использование CAST и CONVERT
В этом примере извлекается имя продукта для тех продуктов, которые имеют первую 3 цифру их цены на список, и преобразует ListPrice эти продукты в int. Она использует AdventureWorksDW2022 базу данных.
SELECT EnglishProductName AS ProductName, ListPrice
FROM dbo.DimProduct
WHERE CAST(ListPrice AS int) LIKE '3%';
В этом примере показан тот же запрос, который используется CONVERT вместо CAST. В нем используется база данных AdventureWorksDW2022.
SELECT EnglishProductName AS ProductName, ListPrice
FROM dbo.DimProduct
WHERE CONVERT(INT, ListPrice) LIKE '3%';
M. Использование CAST с арифметическими операторами
В этом примере вычисляется отдельное значение столбца путем деления цены единицы товара (UnitPrice) на процент скидки (UnitPriceDiscountPct). Затем этот результат округляется до ближайшего целого числа и, наконец, преобразуется в тип данных int . В этом примере используется база данных AdventureWorksDW2022.
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
N. Использование CAST с предложением LIKE
В этом примере столбец ListPrice типа money преобразуется в тип int, а затем в тип char(20) так, чтобы его можно было использовать в предложении LIKE. В этом примере используется база данных AdventureWorksDW2022.
SELECT EnglishProductName AS Name, ListPrice
FROM dbo.DimProduct
WHERE CAST(CAST(ListPrice AS INT) AS CHAR(20)) LIKE '2%';
O. Использование CAST и CONVERT с данными datetime
В этом примере отображается текущая дата и время, используется CAST для изменения текущей даты и времени на тип данных символов, а затем используется CONVERT отображение даты и времени в формате ISO 8601. В этом примере используется база данных AdventureWorksDW2022.
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 . В этом примере используется база данных AdventureWorksDW2022.
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)
- Поддержка параметров сортировки и Юникода