SQLで数値および日付/時刻/タイムスタンプをフォーマットする

この記事では、最近、たまたま私が使用することが多いSQLスカラー関数について紹介したいと思います。SQL selectステートメントを使用しているときに、数値または日付をフォーマットしたいという場面があると思います。RPGで%EDITCまたは%EDITW組み込み関数を、あるいはDDSでEDTCDEまたはEDTWRDキーワードを使用するのと同じようなことです。SQLでは、VARCHAR_FORMATまたはTO_CHARスカラー関数（これらはお互いに同義語であり、両者はまったく同じように動作します）を使用することで、同じ機能を利用することができます。

この記事ではTO_CHARを使用しますが、それらはお互いに同義語であるので、必要に応じてVARCHAR_FORMATに読み換えてください。

TO_CHARの使い方を説明するために、以下のように、4つのグローバル変数を作成してデータを設定しました。

create variable stamp timestamp;
create variable isdate date;
create variable amount decimal (7, 2);
create variable negative_amount decimal (7, 2);

set stamp = current timestamp;  -- 2019-12-29 16:32:40.889085
set isdate = current date;      -- 2019-12-29
set amount = 1234.56;           -- 1234.56
set negative_amount = -1234.56; -- -1234.56

TO_CHARのフォーマットは、次のようになります。

TO_CHAR(numeric expression or timestamp expression, format string)

1つ目のパラメーターは、評価される数値または日付/時刻/タイムスタンプ式で、2つ目のパラメーターは、必要とされるフォーマットです。このフォーマットは、値のフォーマット設定方法に関するテンプレートです。

フォーマットのルールは、ご想像の通り、数値と日付/時刻/タイムスタンプとで異なります。まずは、数値から見て行きましょう。

テンプレートでは、0または9を使用して、それぞれの有効数字が表されます。0の場合、先頭の0は0として表示されます。9の場合、先頭の0はブランクに置換されます。

values 
 to_char(amount, '99999.99') 
,to_char(negative_amount, '99999.99')
,to_char(amount, '00000.00') 
,to_char(negative_amount, '00000.00');

テンプレートは変数の定義に一致する必要があり、小数点の配置は小数の定義に一致する必要があります。小数点以下の桁数が少な過ぎる場合、数値は四捨五入されます。小数点以下の桁数が多過ぎる場合は、0が追加されます。

values 
 to_char(amount, '99999.9') 
,to_char(amount, '99999.999')
,to_char(amount, '9999.99')
,to_char(amount, '999.99');

文字列の先頭にドル記号を追加することができ、数値グループ区切りとしてコンマを指定することができます。通貨記号にドル記号、数値グループ区切りにコンマ、小数点にピリオドを使用するのが要件に合わない場合は、通貨記号文字にL、数値グループ区切り文字にG、小数点文字にDを使用することもできます。これら3つの値は、ライブラリー リストのメッセージ ファイルQCPFMSGのメッセージCPX8416から取得される情報が基になります。これらの文字は大文字でなければならないことに注意してください。

values 
 to_char(amount, '$09,999.99PR')
,to_char(negative_amount, '$09,999.99') 
,to_char(amount, 'L09G999D99')
,to_char(negative_amount, 'L09G999D99');

マイナス記号は、MIを使用して数値の最後に置くことができ、あるいは、PRを使用して負の数を山括弧で囲むこともできます。

values 
 to_char(amount, 'L09G999D99MI')
,to_char(negative_amount, 'L09G999D99MI')
,to_char(amount, 'L09G999D99PR')
,to_char(negative_amount, 'L9999D99PR');

日付、時刻、またはタイムスタンプをフォーマットするときは、指定する変数はタイムスタンプとしてキャストされます。フォーマット テンプレートは、区切り文字として、マイナス記号、ピリオド、スラッシュ、コンマ、アポストロフィー、セミコロン、コロン、またはブランクを使用することができます。特殊なフォーマット文字の大文字小文字の違いは、名前では有意ですが、数値では関係ありません。

日の識別には、月内での日の番号にDD、曜日名にDAY（Dayまたはday）、曜日の省略名にDY（Dyまたはdy）が使用されます。

月の識別には、月番号にMM、月名にMONTH（Monthまたはmonth）、月の省略名にMON（Monまたはmon）が使用されます。

年の識別には、4桁、3桁、2桁、1桁の年に、YYYY、YYY、YY、Yが使用されます。

values 
 to_char(isdate, 'yyyy-mm-dd')  -- *ISO
,to_char(isdate, 'mm-dd-yyyy')  -- *USA
,to_char(isdate, 'Day, dd Month yyyy')
,to_char(isdate, 'Dy, dd Mon yyyy')
,to_char(isdate, 'DY, dd MON yyyy');

時間の識別には、12時間形式にHH（またはHH12）、24時間形式にHH24が使用されます。

分の識別には、MIが使用されます。

AM、A.M.、PM、P.M.は、午前/午後の指定子として使用されます。

values 
 to_char(stamp, 'hh24:miAM Day, dd Month yyyy')
,to_char(isdate, 'hh24:mi Day, dd Month yyyy');

その他の有用な日付フォーマット エレメントには、ユリウス日用のJ、曜日番号用のD、ISO曜日番号用のID、年間通算日用のDDD、ISO曜日番号用のID、月内の週番号用のW、年内の週番号用のWW、年内のISO週番号用のIW、四半期用のQ、ISO年用のIYYYY（IYYY、IYY、IY）、世紀用のCCなどがあります。

タイムスタンプの処理では、秒にSS、直近の午前0時からの秒数にSSSSS、ミリ秒にMS、マイクロ秒にUSまたはNNNNNNも使用できます。

VARCHAR_FORMAT/TOCHARが、私が思っているのと同じくらい有用だと思っていただければ幸いです。

あわせて読みたい記事