CAST および CONVERT (Transact-SQL)

expression が日付や時刻のデータ型である場合、style には次の表に示すいずれかの値を指定できます。その他の値は 0 として処理されます。SQL Server では、アラビア式での日付形式がクウェート アルゴリズムによりサポートされます。

1 これらのスタイル値で返される結果は非決定的です。この場合は、すべての (yy) (2 桁の年) スタイルと、(yyyy) (4 桁の年) スタイルのサブセットが対象となります。

2 既定値 (style0 または 100、9 または 109、13 または 113、20 または 120、21 または 121) では、常に 4 桁の年 (yyyy) が返されます。

3datetime 型に変換する場合は入力になり、文字データに変換する場合は出力になります。

4 XML で使用するよう設計されています。datetime または smalldatetime 型から文字データに変換する場合の出力形式は、前の表に示したとおりです。

5 Hijri とはいくつかのバリエーションがあるカレンダー システムです。SQL Server ではクウェート アルゴリズムが使用されます。

重要
SQL Server の既定では、2 桁の年は、終了年 2049 年を基準に解釈されます。つまり、2 桁の年である 49 年は 2049 年、50 年は 1950 年と解釈されます。クライアント アプリケーションの中には、オートメーション オブジェクトをベースとするクライアント アプリケーションのように、2030 年を終了年とするものも多くあります。SQL Server では構成オプション two digit year cutoff が用意されており、このオプションを使用して SQL Server の終了年の設定を変更でき、一貫した日付の処理を行うことができます。年は 4 桁で指定することを推奨します。

6 文字データを datetime または smalldatetime 型にキャストする場合のみサポートされます。日付部分または時刻部分のみを表す文字データは、datetime または smalldatetime データ型にキャストされます。時刻部分の指定がない場合、時刻は 00:00:00:000 に設定され、日付部分の指定がない場合、日付は 1900-01-01 に設定されます。

7. タイム ゾーン インジケーター Z は省略可能です。これを使用すると、タイム ゾーン情報が含まれる XML の datetime 値から、タイム ゾーン情報が含まれない SQL Server datetime 値へのマップが容易になります。Z は、タイム ゾーン UTC-0 を示すインジケーターです。他のタイム ゾーンは、+ または - の方向に HH:MM オフセットで示されます。たとえば、2006-12-12T23:45:12-08:00 のようになります。

smalldatetime 型を文字データに変換する場合、秒またはミリ秒を含むスタイルではそれらの位置に 0 が表示されます。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 のように hh と mm の両方が常に 2 桁である必要があります。

注
Unicode データでは常に偶数のバイト数が使用されるので、binary 型または varbinary 型と、Unicode がサポートするデータ型との間でデータを変換する場合は注意してください。たとえば、SELECT CAST(CAST(0x41 AS nvarchar) AS varbinary) という変換を行うと、16 進数の 41 ではなく、4100 が返されます。

大きな値のデータ型では、それより小さな値のデータ型、つまり varchar、nvarchar および varbinary 型と同様の明示的および暗黙的な変換動作が行われます。ただし、次のガイドラインを考慮する必要があります。

• image 型から varbinary(max) 型への変換およびその逆の変換は暗黙的な変換であり、text 型と varchar(max) 型の間、および ntext 型と nvarchar(max) 型の間の変換も暗黙的な変換です。

• varchar(max) 型のような大きな値のデータ型から、varchar 型のような小さな値のデータ型への変換は暗黙的な変換ですが、大きな値が、小さなデータ型で指定された長さに対して大きすぎると、切り捨てが発生します。

• varchar、nvarchar、または varbinary 型からそれぞれ対応する大きな値のデータ型への変換は、暗黙的に実行されます。

• sql_variant 型から大きな値のデータ型への変換は明示的な変換です。

• 大きな値のデータ型は、sql_variant 型へは変換できません。

Microsoft .NET Framework 共通言語ランタイム (CLR) ユーザー定義型の変換方法については、「ユーザー定義型に対する操作」を参照してください。xml 型からの変換方法の詳細については、「XML インスタンスの生成」を参照してください。

明示的か暗黙的かにかかわらず、xml データ型を文字列またはバイナリのデータ型にキャストするとき、xml データ型の内容は一連のルールに基づいてシリアル化されます。このルールの詳細については、「XML データのシリアル化」を参照してください。XML から CLR ユーザー定義型へのキャスト方法の詳細については、「ユーザー定義型に対する操作」を参照してください。他のデータ型から xml データ型への変換方法については、「XML インスタンスの生成」を参照してください。

データ型の自動変換は、text 型および image 型ではサポートされません。text データは文字データに、image データは binary または varbinary 型に明示的に変換できますが、最大の長さは 8,000 バイトに制限されます。文字を含む文字式を int 型に変換するなど、不適切な変換が試行された場合、SQL Server ではエラー メッセージが返されます。

CAST または CONVERT の入出力が文字列である場合、出力では入力と同じ照合順序および照合順序ラベルが使用されます。入力が文字列ではない場合、出力ではデータベースの既定の照合順序、および強制可能な既定照合順序の照合順序ラベルが使用されます。詳細については、「照合順序の優先順位 (Transact-SQL)」を参照してください。

出力に別の照合順序を割り当てるには、CAST 関数または CONVERT 関数の結果式に COLLATE 句を適用します。次に例を示します。

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

char、nchar、nvarchar、varchar、binary、varbinary 型などの文字式やバイナリ式を異なるデータ型に変換する場合は、データを切り捨てて一部分だけが表示されることがあります。または、結果が短すぎて表示できないというエラーが返されます。char、varchar、nchar、nvarchar、binary、および varbinary 型への変換では、次の表に示す変換を除き、切り捨てが行われます。

* = 結果が短すぎて表示できない。E = 結果が短すぎて表示できない場合エラーが返される。

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)

数値ではない char、nchar、varchar、または nvarchar 型のデータが int、float、numeric、または decimal 型に変換されるとき、SQL Server ではエラー メッセージが返されます。SQL Server では、空文字列 (" ") が numeric または decimal 型に変換されるときにもエラーが返されます。

SQL Server 2000 では、文字列から日付と時刻への変換は "決定的" とマークされます。ただし、これは次の表に示すスタイルに対しては当てはまりません。これらのスタイルの場合、その変換は言語の設定に依存します。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

次の例では、今年のこれまでの売り上げ合計 (SalesYTD) を手数料のパーセンテージ (CommissionPCT) で割り、単一列の計算 (Computed) を行います。この結果は、最も近い整数に丸められた後、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 型に変換して、次に LIKE 句と共に使用できるよう char(20) 列に変換します。

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 data type により型指定された 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)