CONVERT (SQL)
構文
CONVERT(type,expression)
CONVERT(type,expression,formatCcode)
{fn CONVERT(expression,type)}
説明
CONVERT 関数は、特定のデータ型の式を別のデータ型の対応値に変換します。CONVERT は CAST と似ていますが、以下の相違点があります。
-
CONVERT のほうが CAST よりも柔軟です。例えば、CONVERT はストリーム・データの変換をサポートし、日付値と時刻値の書式を設定できます。
-
CAST のほうが CONVERT よりもデータベースでの互換性に優れています。CAST が ANSI SQL-92 標準を使用して実装されるのに対し、CONVERT の実装はデータベース固有です。InterSystems SQL が提供する CONVERT の実装には、MS SQL Server および ODBC との互換性があります。
MS SQL Server の互換性
この CONVERT の実装は、MS SQL Server と互換性のある一般的な InterSystems IRIS® スカラ関数です。この関数は日付と時刻の書式設定、およびストリーム・データの変換をサポートします。
-
CONVERT(type,expression) は指定されたデータ型に式を変換します。InterSystems SQL でサポートされているデータ型の一覧については、"データ型" を参照してください。
以下の文は、10 進数 (πの近似値) を文字列に変換し、その数を 4 文字に切り捨てます。
SELECT CONVERT(CHAR(4),3.14159) -- '3.14'
例 :
-
CONVERT(type,expression,formatCode) は、指定されたデータ型に式を変換し、指定された形式コードに基づいて戻り値を書式設定します。
以下の文は、日付の文字列を TIMESTAMP データ型に変換します。この関数は、形式コード 103 (mm/dd/yy の形式を表す) に基づいて入力を変換します。すべての形式コードは、formatCode 引数の説明を参照してください。
SELECT CONVERT(TIMESTAMP,'1/1/99',103) -- '01/01/1999 00:00:00'
例 : 文字列への日付の変換
ODBC 互換性
CONVERT のこの実装は、一般的な InterSystems IRIS ODBC スカラ関数です。この関数は日付と時刻の書式設定をサポートしていません。また、ストリーム・データの変換もサポートしません。
-
{fn CONVERT(expression,type)} は、指定されたデータ型に式を変換します。CONVERT のこの実装では、各データ型の引数の前に SQL_ キーワードを付加する必要があります。これらのデータ型はパラメータを受け入れません。例えば、文字列データ型には最大長を設定できません。数値データ型には精度 (最大桁数) と桁 (小数部の最大桁数) を設定できません。
以下の文は 10 進数を文字列に変換します。返された文字列は切り捨てられません。SQL_VARCHAR(4) などの最大長の指定は許可されません。
SELECT {fn CONVERT(3.14159,SQL_VARCHAR) } -- '3.14159'
例 :
引数
type
変換した expression のデータ型です。指定できる型は、InterSystems IRIS の CONVERT() 構文を使用しているか ODBC の {fn CONVERT()} 構文を使用しているかによって異なります。
CONVERT() 関数
InterSystems IRIS の CONVERT() 構文は "データ型" で説明されているデータ型をサポートします。指定できる一般的なデータ型は以下のとおりです。
-
文字列データ型 : CHAR、CHARACTER、VARCHAR。文字列のタイプによっては、必要に応じて最大長パラメータを指定できます。例 : VARCHAR(10)
-
数値データ型 : INTEGER、DECIMAL、DOUBLE、MONEY。数値の種類によっては、必要に応じて精度と桁のパラメータを指定できます。例 : DECIMAL(8,4)
-
日付と時刻のデータ型 : DATE、TIME、TIMESTAMP、POSIXTIME
-
ビットおよびバイナリ・データ型 : BIT、BINARY、VARBINARY
{fn CONVERT()} 関数
ODBC の {fn CONVERT()} 構文は、CONVERT() 構文よりも限定的なデータ型のセットをサポートします。サポートされるデータ型は CONVERT() 構文に指定するデータ型に対応していますが、先頭に SQL_ キーワードを付加する必要があります。
指定できる有効なデータ型を 2 つのグループに分けて以下の表に示します。
-
1 番目のグループはデータ値とデータ型の両方を変換します。例えば、%Date ソースを SQL_VARCHAR に変換すると、日付がテキスト値に変わり、クエリではその値が VARCHAR データ型として処理されます。
-
2 番目のグループは、データ型を変換しますがデータ値を変換しません。例えば、%Date ソースを INTEGER に変換しても %Date ソースは変化しませんが、クエリでは整数形式の日付が INTEGER データ型として処理されます。
ソース | 有効な変換タイプ (型と値を変換) | 有効な変換タイプ (型のみを変換) |
---|---|---|
任意の数値データ型 | SQL_VARCHAR、SQL_DOUBLE、SQL_DATE、SQL_TIME | N/A |
%String | SQL_DATE、SQL_TIME、SQL_TIMESTAMP | N/A |
%Date | SQL_VARCHAR、SQL_POSIXTIME、SQL_TIMESTAMP | SQL_INTEGER、SQL_BIGINT、SQL_SMALLINT、SQL_TINYINT、SQL_DATE |
%Time | SQL_VARCHAR、SQL_POSIXTIME、SQL_TIMESTAMP | SQL_INTEGER、SQL_BIGINT、SQL_SMALLINT、SQL_TINYINT、SQL_TIME |
%PosixTime | SQL_TIMESTAMP、SQL_DATE、SQL_TIME | SQL_VARCHAR、SQL_INTEGER、SQL_BIGINT、SQL_SMALLINT、SQL_TINYINT |
%TimeStamp | SQL_POSIXTIME、SQL_DATE、SQL_TIME | SQL_VARCHAR、SQL_INTEGER、SQL_BIGINT、SQL_SMALLINT、SQL_TINYINT |
任意の非ストリーム・データ型 | SQL_INTEGER、SQL_BIGINT、SQL_SMALLINT、SQL_TINYINT | SQL_DOUBLE |
この関数にデータ型を指定するときは、以下の点に注意してください。
-
SQL_VARCHAR は標準的な ODBC 表現です。SQL_VARCHAR に変換する場合、日時は適切な ODBC 表現に変換されます。つまり、数値のデータタイプは文字列に変換されます。SQL_VARCHAR から変換する場合、値は有効な ODBC Time、Timestamp、Date 表現である必要があります。
-
時刻値を SQL_TIMESTAMP または SQL_POSIXTIME に変換すると、指定されていない日付は既定で 1841-01-01 になります。CONVERT() 構文では、日付は既定値の 1900-01-01 となります。
-
日付値を SQL_TIMESTAMP または SQL_POSIXTIME に変換すると、時刻は既定で 00:00:00 になります。
-
秒の小数部の前には、ピリオド (.) とコロン (:) のいずれかを記述できます。これらの記号は、意味が異なります。ピリオドは標準的な小数を示すため、12:00:00.4 は 10 分の 4 秒を示し、12:00:00.004 は 1000 分の 4 秒を示します。コロンは、それに続く値が 1000 分の 1 秒単位であることを示すため、12:00:00:4 は 1000 分の 4 秒を示します。コロンの後に続けることができる桁数は 3 に制限されています。
-
整数データ型または SQL_DOUBLE データ型への変換の場合、日付と時刻も含め、CONVERT 関数によってデータ値が数値表記に変換されます。SQL_DATE の場合は 1841 年 1 月 1 日以降の日数となります。SQL_TIME の場合、これは午前 0 時以降の経過秒数となります。CONVERT で数値以外の文字が検出されると、その文字で入力文字列が切り捨てられます。整数データ型では、小数点以下が切り捨てられ、数値の整数部分のみが返されます。
expression
新しいデータ型への変換元とする式です。expression には、単一の文字列値などのスカラまたはテーブル列などの非スカラを指定できます。expression の有効な値は、type で指定したデータ型によって異なります。
-
定義されたデータ型 (ObjectScript で提供されるホスト変数など) が expression にない場合、そのデータ型は既定で文字列データ型に設定されます。
-
expression にストリーム・データがある場合に {fn CONVERT(expression,type)} 構文を使用すると、CONVERT から SQLCODE -37 エラーが発行されます。
-
expression が NULL の場合、指定した型に関係なく、変換された値は NULL のままとなります。
-
expression が空の文字列 ('') または数値以外の文字列である場合、返される値は指定した type によって異なります。
-
type が文字列データ型の場合、CONVERT からは指定した値が返されます。
-
type が数値データ型または TIME、SQL_TIME、SQL_DATE 型の場合、CONVERT からは 0 (ゼロ) が返されます。
-
無効な値を type に指定すると SQLCODE -141 エラーが発生します。
formatCode
日付、日付時刻、時刻の各形式を指定する整数コードです。
formatCode を使用して、日付、時刻、タイムスタンプのデータ型から文字列に変換するときの出力を定義します。以下に例を示します。
SELECT CONVERT(VARCHAR,TO_DATE('22 FEB 2022'),1) -- '02/22/22'
文字列から日付、時刻、タイムスタンプのデータ型に変換するときに、formatCode を使用して入力を定義することもできます。以下に例を示します。
SELECT CONVERT(DATE,'22 FEB 2022',106) -- '02/22/2022'
CONVERT() 構文のみが formatCode をサポートします。
無効な形式または formatCode に一致しない形式で expression を指定すると、SQLCODE -141 エラーが生成されます。存在しない formatCode を指定すると、1900-01-01 00:00:00 が返されます。
以下の表に、サポートされている形式コードを示します。
-
1 列目は 2 桁の年を出力するコードの一覧です。
-
2 列目は 4 桁の年を出力するコード、または年を出力しないコードの一覧です。
2 桁の年コード | 4 桁の年コード | 形式 |
---|---|---|
N/A | 0 または 100 | Mon dd yyyy hh:mmAM (または PM) |
1 | 101 | mm/dd/yy |
2 | 102 | yy.mm.dd |
3 | 103 | dd/mm/yy |
4 | 104 | dd.mm.yy |
5 | 105 | dd-mm-yy |
6 | 106 | dd Mon yy |
7 | 107 | Mon dd, yy (dd < 10 のとき先頭に 0 は入りません) |
N/A | 8 または 108 | hh:mm:ss |
N/A | 9 または 109 | Mon dd yyyy hh:mm:ss:nnnAM (または PM) |
10 | 110 | mm-dd-yy |
11 | 111 | yy/mm/dd |
12 | 112 | yymmdd |
N/A | 13 または 113 | dd Mon yyyy hh:mm:ss:nnn (24 時間制) |
N/A | 14 または 114 | hh:mm:ss.nnn (24 時間制) |
N/A | 20 または 120 | yyyy-mm-dd hh:mm:ss (24 時間制) |
N/A | 21 または 121 | yyyy-mm-dd hh:mm:ss.nnn (24 時間制) |
N/A | 126 | yyyy-mm-ddThh:mm:ss.nnn (24 時間制) |
N/A | 130 | dd Mon yyyy hh:mm:ss:nnnAM (または PM) |
N/A | 131 | dd/mm/yyyy hh:mm:ss:nnnAM (または PM) |
値の範囲
許容される日付の範囲は 0001-01-01 から 9999-12-31 です。
既定値
CONVERT() 構文の場合、時刻値を TIMESTAMP、POSIXTIME、DATETIME、または SMALLDATETIME に変換すると、日付は既定で 1900-01-01 になります。{fn CONVERT()} 構文の場合、日付は既定で 1841-01-01 となります。
日付値を TIMESTAMP、POSIXTIME、DATETIME、または SMALLDATETIME に変換すると、時刻は既定で 00:00:00 になります。
既定の形式
formatCode を指定しない場合は、指定された値から CONVERT が形式を決定しようとします。形式を決定できない場合には、既定で formatCode 100 (mm-dd-yy) になります。
2 桁の年
00 から 49 までの 2 桁の年は 21 世紀の日付 (2000 年から 2049 年まで) に変換されます。
50 から 99 までの 2 桁の年は 20 世紀の日付 (1950 年から 1999 年まで) に変換されます。
秒の小数部
秒の小数部をピリオド (.) またはコロン (:) で始めることができます。これらの記号は、意味が異なります。
-
ピリオド (既定) — すべての formatCode 値で有効です。ピリオドは標準の小数部であることを示します。例えば、12:00:00.4 の秒数は 10 分の 4 秒、12:00:00.004 の秒数は 1000 分の 4 秒です。CONVERT では、小数部の桁数に制限はありません。
-
コロン — formatCode 値が 9/109、13/113、14/114、130、131 の場合にのみ有効です。コロンは、次に続く数が 1000 分の秒数であることを示します。例えば、12:00:00:4 の秒数は 1000 分の 4 秒 (12:00:00.004) です。小数部には最大で 3 桁を指定できます。
例
数値型間の変換
以下の例では、DECIMAL と DOUBLE のデータ型を使用した小数の変換を比較しています。InterSystems IRIS の CONVERT() 構文を使用しています。DOUBLE への変換によって精度が低下します。
SELECT CONVERT(DECIMAL,-123456789.0000123456789) AS DecimalVal, -- -123456789.0000123457
CONVERT(DOUBLE,-123456789.0000123456789) AS DoubleVal -- -123456789.00001235306
以下の文は、ODBC の {fn CONVERT()} 構文を使用して上記と同様の変換を実行します。この構文は DECIMAL データ型をサポートしていないことから、値は DOUBLE データ型にのみ変換されます。
SELECT {fn CONVERT(-123456789.0000123456789,SQL_DOUBLE) } AS DecimalVal -- -123456789.00001235306
文字列間の変換
この例は、expression よりも文字列長が短い出力文字列長を指定し、VARCHAR から VARCHAR への変換で文字列を切り詰めています。切り詰めは、InterSystems IRIS の CONVERT() 構文でのみサポートされています。ODBC の {fn CONVERT()} 構文で唯一サポートされている文字列形式は SQL_VARCHAR です。
SELECT CONVERT(VARCHAR(5),'Hello, World') As TruncatedValue -- 'Hello'
文字データ型で文字列長を指定していない場合、最大長は既定で 30 文字になります。
SELECT CONVERT(VARCHAR,'This string is more than 30 characters.') --This string is more than 30 ch
SELECT {fn CONVERT('This string is more than 30 characters.',SQL_VARCHAR) } --This string is more than 30 ch
CONVERT() 構文の場合、BINARY 型または VARBINARY 型への変換にも、この最大長が適用されます。または、長さの指定されていないデータ型は、データ型テーブルに示すように、MAXLEN が 1 の文字にマップされます。
文字列へのストリーム・データの変換
以下の例では、文字ストリーム・フィールドを VARCHAR テキスト文字列に変換します。また、CHAR_LENGTH を使用して、文字ストリーム・フィールドの長さを表示します。
SELECT Notes,CONVERT(VARCHAR(80),Notes) AS NoteText,CHAR_LENGTH(Notes) AS TextLen
FROM Sample.Employee WHERE Notes IS NOT NULL
ODBC の {fn CONVERT()} 構文は文字ストリームをサポートしていません。
文字列への日付の変換
以下の例は、DOB (誕生日) 列の日付を SQL_VARCHAR データ型に変換します。ここで得られる文字列は yyyy-mm-dd 形式で表されます。
SELECT DOB,CONVERT(VARCHAR,DOB) AS DOBtoVChar
FROM Sample.Person
SELECT DOB,{fn CONVERT(DOB,SQL_VARCHAR)} AS DOBtoVChar
FROM Sample.Person
以下の例では、誕生日フィールド (DOB) から、形式設定した文字列への変換をいくつか示しています。それぞれの変換後、出力例の日付文字列がコメントに表示されます。
SELECT DOB,
CONVERT(VARCHAR(20),DOB) AS DOBDefault, -- Mar 20 1983 12:00AM
CONVERT(VARCHAR(20),DOB,100) AS DOB100, -- Mar 20 1983 12:00AM
CONVERT(VARCHAR(20),DOB,107) AS DOB107, -- Mar 20, 1983
CONVERT(VARCHAR(20),DOB,114) AS DOB114, -- 00:00:00.000
CONVERT(VARCHAR(20),DOB,126) AS DOB126 -- 1983-03-20T00:00:00:
FROM Sample.Person
既定の形式とコード 100 の形式は同じです。DOB フィールドには時刻値が格納されていないため、時刻を表示する形式 (ここでは、既定値、100、114、126) では、12:00AM (真夜中) を示すゼロ値が指定されます。コード 126 の形式は、日付と時刻の文字列を空白なしで表示します。
InterSystems IRIS の CONVERT() 構文のみが日付文字列の書式設定をサポートしています。ODBC の {fn CONVERT()} 構文ではサポートしていません。
数値型への文字列の変換
以下の例では、複数の文字列の混在が整数に変換されます。InterSystems IRIS では、最初の数値以外の文字で文字列を切り捨て、結果として得られる数値をキャノニック形式に変換します。
SELECT CONVERT(INTEGER,'007 James Bond') -- 7
SELECT {fn CONVERT('007 James Bond',SQL_INTEGER)} -- 7
タイムスタンプへの日付の変換
以下の例は、DOB (誕生日) 列の日付をタイムスタンプ・データ型に変換します。ここで得られるタイムスタンプは、yyyy-mm-dd hh:mm:ss 形式で表されます。
SELECT DOB,CONVERT(TIMESTAMP,DOB) AS DOBtoTstamp
FROM Sample.Person
SELECT DOB,{fn CONVERT(DOB,SQL_TIMESTAMP)} AS DOBtoTstamp
FROM Sample.Person
数値型への日付の変換
以下の例は、DOB (誕生日) 列の日付を整数データ型に変換します。生成される整数は 1840 年 12 月 31 日からの $HOROLOG カウントになります。
SELECT DOB,CONVERT(INTEGER,DOB) AS DOBtoInt
FROM Sample.Person
SELECT DOB,{fn CONVERT(DOB,SQL_INTEGER)} AS DOBtoInt
FROM Sample.Person
ビット値の変換
BIT データ型変換を実行できます。許容される値は、1、0、または NULL です。その他の値を指定すると、InterSystems IRIS によって SQLCODE -141 エラーが発行されます。この例では NULL に対する 2 種類の BIT 変換を示します。
SET a=""
&sql(SELECT CONVERT(BIT,:a),
CONVERT(BIT,NULL)
INTO :x,:y)
WRITE !,"SQLCODE=",SQLCODE
WRITE !,"the host variable is:",x
WRITE !,"the NULL keyword is:",y
埋め込み SQL を使用してホスト変数に空の文字列を格納している場合にのみ、ビット変換でその文字列を指定できます。CONVERT(BIT,'') のように空の文字列を直接指定すると、InterSystems IRIS によって SQLCODE -141 エラーが発行されます。
詳細
CONVERT クラス・メソッド
以下の構文で示すように、データ型を SQL_ キーワードとして指定し、CONVERT()Opens in a new tab メソッドを呼び出すことで、データ型を変換することもできます。
$SYSTEM.SQL.Functions.CONVERT(expression,SQL_convertToType,SQL_convertFromType)
以下に例を示します。
write $SYSTEM.SQL.Functions.CONVERT(66225,"SQL_VARCHAR","SQL_DATE")