SQLを使用した日付および時刻変換
多くのIBM iのショップでは、日付および時刻を数値および文字フィールドに格納しているため、それらのシステムをプログラムする人は、利用可能なすべての日付および時刻変換ツールについて理解しておくことが望ましいと言えます。先日のRPG & DB2 Summitで他の出席者と話していた際に、いくつかのSQL変換手法についてこれまで記事に取り上げてこなかったことに気付かされました。
IBM iプログラマーは、少なくとも2つの理由から、日付、時刻、およびタイムスタンプ データを1つのフォーマットから別のフォーマットへ変換する必要が生じることがあります。1つには、数値および文字フィールドで日付時刻の演算を行うことができないことです。2つには、顧客は、自分たちが慣れているフォーマットで日付および時刻を読み取りたいと思うものであるため、そうしたフォーマットにすぐに対応できる必要があります。
タイムスタンプへ変換する:
例として、注文表（物理ファイル）について考えてみます。販売注文、製造注文、組立注文など、様々な注文があります。期限の列（フィールド）を持つものであれば、どのような種類の注文でも構いません。この例では、注文番号と期限の2つの列について考える必要があるだけです。また、注文番号がどのように定義されるかは問題ではありません。重要なのは、どのように期限が定義されるかです。まずは、期限が8桁のパック10進数またはゾーン10進数フィールドとして定義されている場合について考えてみます。
期限が翌日から30日以内であるすべての注文を選択しますが、期限を過ぎた注文は含みません。
TIMESTAMP_FORMAT関数は、フォーマット文字列で指定されているパターンに基づいて文字データをタイムスタンプに変換します。フォーマット文字列のYYYYMMDDという値は、年が4桁、月が2桁、および日が2桁の形式で日付が保存されることを意味します。TIMESTAMP_FORMATについての詳細は、DB2 for i 6.1の機能強化に関してMichael Sansoterraが記した素晴らしい記事をお読みください。
期限が数値であるため、CHAR関数を使用してその数値を文字データに変換します。
一部のショップでは、7桁のパック10進数の列に日付を格納しています。
クエリーは、ほんの少し異なるだけです。
日付へ19,000,000を足すことで、前の例で使用したYYYYMMDDフォーマットへ変換します。TIMESTAMP_FORMATには、1桁の世紀標識を示す方法がありません。
8バイト文字の日付を使用しているショップでは、さらにより簡単なクエリーを使用します。これは日付データがすでに文字フォーマットであるためです。
タイムスタンプを変換する:
時には、反対方向への変換が必要になることもあります。日付、時刻、またはタイムスタンプを他のデータ型へ変換することが必要になるケースです。これも簡単に行えます。TIMESTAMP_FORMATの対となる関数として、VARCHAR_FORMAT関数があります。
TIMESTAMP_FORMATと同様に、VARCHAR_FORMATは、変換されるデータとフォーマット文字列の2つの引数を使用します。詳細は、Michael Sansoterraの記事を参照してください。
ユーザーが期限そのものを調整することが許可されておらず、代わりに、期限の調整が必要な注文を指示する方法はあるものとします。注文番号は、ORDER01という表に保存されています。バッチ処理で、アップデートが適用されます。以下に、期限がどのように定義されているかに応じて、必要となるSQLの3つの形式を示します。
3つのクエリーはすべて、期限を現在の日付の5日後に設定します。
日付変換の手法はこれらだけではありません（FMTDATE関数について記した記事を参照）。しかし、これらは有用な手法であり、日付および時刻データを扱う誰もがそれらをマスターしておくことを私は推奨します。
