SQL Serverでは、T-SQLのFORMAT()関数を使用して、数値や日付などの値をフォーマットされた文字列として返すことができます。
フォーマットする値を指定し、使用するフォーマットを指定します。
フォーマットされる値を与え、使用するフォーマットを指定します。この関数は、値をフォーマットする際に使用するカルチャを指定するオプションの引数を受け入れます。
構文
構文は次のようになります:
FORMAT ( value, format )
valueformat は希望するフォーマットを指定する有効なフォーマット文字列です。
オプションのculture引数は、カルチャを指定するために使用できます。
FORMAT 関数は非決定論的です。
Example 1 – Format a Number
ここでは、数字をフォーマットする例を示します:
SELECT FORMAT(1, 'N') AS Result;
結果です。
+----------+| Result ||----------|| 1.00 |+----------+
この例では、NNnのどちらでも構いません。
例2 – 通貨へのフォーマット
以下は、数字を通貨としてフォーマットする例です：
SELECT FORMAT(1, 'C') AS Result;
結果です。
+----------+| Result ||----------|| $1.00 |+----------+
その他の数値の書式
FORMAT() 関数で使用できる書式文字列は他にもたくさんあります。 たとえば、パーセント、固定小数点、指数（科学）、16進数などの書式文字列があります。
数字の書式文字列は、標準とカスタムに分かれています。
次の 2 つの記事では、すべての数値フォーマット文字列を例を含めて紹介しています。
- 標準の数値フォーマット文字列
- カスタムの数値フォーマット文字列
また、数値のフォーマットの例については、「SQL Server で数値をフォーマットする方法」を参照してください。
例 3 – 日付の書式設定
以下に日付の書式設定の例を示します:
SELECT GETDATE() AS 'Unformatted Date', FORMAT( GETDATE(), 'D') AS 'Formatted Date';
結果です。
+-------------------------+------------------------+| Unformatted Date | Formatted Date ||-------------------------+------------------------|| 2019-05-08 06:16:55.613 | Wednesday, May 8, 2019 |+-------------------------+------------------------+
ここでは、長い日付のパターンを指定するDを使用しました。
その他の日付フォーマット
日付のフォーマットに使用できるフォーマット文字列は他にもたくさんあります。 数字の書式文字列と同様に、日付と時刻の書式文字列も標準とカスタムに分かれているので、自分でカスタムの書式を作ることもできますし、標準のものに頼ることもできます。
以下の 2 つの記事では、すべての日付と時刻の書式文字列を例を含めて紹介しています。
- 標準の日付と時刻の書式文字列
- カスタムの日付と時刻の書式文字列
また、より多くの例については、「SQL Server で日付 & 時刻を書式設定する方法」を参照してください。
例 4 – オプションの culture 引数
ここでは、culture 引数を使用してさまざまな通貨の値を返す例を示します。
SELECT FORMAT(1, 'C', 'fr-FR') AS 'France', FORMAT(1, 'C', 'th-TH') AS 'Thailand', FORMAT(1, 'C', 'ja-JP') AS 'Japan';
Result:
FORMAT() 関数は、.NET Frameworkでサポートされている任意のカルチャを引数として受け入れます。
その他の例については、How Language Settings can Affect your FORMAT() Resultsをご覧ください。
例 5 – 無効な culture 引数
無効な culture を指定すると、次のようなエラーが表示されます:
SELECT FORMAT(1, 'C', 'oop-SS!') AS 'Oops!';
結果。
The culture parameter 'oop-SS!' provided in the function call is not supported.
例 6 – 無効なフォーマット値
しかし、その他のエラーの場合は、NULLが返されます。
SELECT FORMAT(GETDATE(), 'C') AS 'Result';
Result:
+----------+| Result ||----------|| NULL |+----------+
このケースでは、日付を通貨に変換しようとしていたため、結果は NULL となりました。
有効なデータ型
FORMAT() は、.NET Framework Common Language Runtime (CLR) の存在に依存しており、CLR の書式規則を使用します。
以下のデータ型は FORMAT() 関数で書式設定できます。
| Category | Type | .NETタイプ |
|---|---|---|
| Numeric | bigint | Int64 |
| Numeric | int | Int32 |
| Numeric | smallint | Int16 |
| Numeric | tinyint | Byte |
| Numeric | decimal | SqlDecimal |
| Numeric | numeric | SqlDecimal |
| Numeric | floattd | Double |
| Numeric | real | Single |
| Numeric | smallmoney | Decimal |
| Numeric | smallmoney | Decimal |
| Numeric | money | decimal |
| 日時 | 日時 | |
| 日時 | 時間 | TimeSpan |
| Date and Time | datetime | DateTime |
| Date and Time | smalldatetime | DateTime |
| 日付と時刻 | datetime2 | DateTime |
| 日付と時刻 | datetimeoffset | DateTimeOffset |
Escaping Colons and Periods for the ‘time’ Data Type
FORMATを使用する際には、コロンとピリオドをエスケープする必要があります。 を使用する場合、コロンとピリオドをエスケープする必要があります（これは、CLRのフォーマット規則に準拠しています）。 したがって、フォーマット文字列（第2パラメータ）にコロンやピリオドが含まれている場合、入力値（第1パラメータ）が時刻データ型の場合は、コロンやピリオドをバックスラッシュでエスケープする必要があります。
例:
SELECT CAST('12:15' AS time) AS 'Unformatted Data', FORMAT(CAST('12:15' AS time), N'hh.mm') AS 'Unescaped', FORMAT(CAST('12:15' AS time), N'hh\.mm') AS 'Escaped';
結果:
+--------------------+-------------+-----------+| Unformatted Data | Unescaped | Escaped ||--------------------+-------------+-----------|| 12:15:00 | NULL | 12.15 |+--------------------+-------------+-----------+
従って、予想通り、エスケープされていない文字列は NULL を返します。
前述したように、これはtimeのデータ型にのみ適用されます。入力値を別のデータ型に変更した場合は、エスケープする必要はありません。
SELECT CAST('12:15' AS datetime) AS 'Unformatted Data', FORMAT(CAST('12:15' AS datetime), N'hh.mm') AS 'Unescaped', FORMAT(CAST('12:15' AS datetime), N'hh.mm') AS 'Escaped';
Result:
+-------------------------+-------------+-----------+| Unformatted Data | Unescaped | Escaped ||-------------------------+-------------+-----------|| 1900-01-01 12:15:00.000 | 12.15 | 12.15 |+-------------------------+-------------+-----------+
この場合、入力値はdatetimeなので、エスケープしなくても結果は問題ありません。
また、バックスラッシュを使用して、結果文字列に含めたい他の文字をエスケープすることもできますが、その場合はフォーマット指定子として解釈されます。 文字の前にバックスラッシュを付けると、それに続く文字が文字リテラルであり、結果文字列にそのまま含まれることを意味します。
カスタムの日付と時刻のフォーマット文字列では、dfFghHKmMstyz:/の文字は、リテラル文字としてではなく、カスタムフォーマット指定子として解釈されます。
数字のカスタムフォーマット文字列では、#0.,%‰E+-の記号も、フォーマット文字列の中での位置によっては、フォーマット指定子として解釈されます。
結果文字列にバックスラッシュを含める必要がある場合は、別のバックスラッシュでエスケープしてください。
リモート
FORMAT() 関数は CLR の存在に依存しているため、リモートすることはできません。 CLR を必要とする関数をリモートすると、リモート サーバーでエラーが発生する可能性があります。
FORMAT()関数を使用する場合
マイクロソフトでは、日付/時刻や数値を文字列としてローカライズしてフォーマットする場合は FORMAT()CAST()CONVERT() 関数を使用することを推奨します。