データベースを運用するにあたり、年、月、日のデータを数値型で保持することがあり、これら値を用いて日付データの検索をする場面が多々出てきます。
しかしながら、数値型と日付/時刻型ではデータの型式が異なることからそのまま抽出条件に設定することができません。そんなときには、「DateSerial関数」を活用します。
今回は、DateSerial関数の使用方法と、DateSerial関数を用いて月初と月末を動的に設定する方法を紹介します。
DateSerial関数について
まずは、DateSerial関数について見ていきましょう。
DateSerial関数は次のとおり定義されます。
DateSerial ( year, month, day )
year: 整数型を指定、100から9999の範囲の数値または数値式を指定する。（省略不可）
month: 整数型を指定、任意の数式を指定。ただし、その値は「1~12」となる。（省略不可）
day: 整数型を指定、任意の数式を指定。ただし、その値は「1~31」となり、かつ月の条件によっては無効な引数を指定すると正しくない結果になることがある。（省略不可）
例えば、DateSerial（2020, 12, 2）とすると、日付/時刻型として「2020/12/2」が返されます。
また、あるテーブルに「年」、「月」及び「日」のフィールドが整数型として設定されている場合、クエリのフィールドに「DateSerial([年], [月], [日])」と記載することで、年月日の日付データとして出力することもできます。
具体的に見ていきましょう。
まず、「T_年月日」テーブルを作成し、「年」、「月」及び「日」フィールドを設定します。またこれらのフィールドは全て数値型とします。
次に、以下のようにサンプルデータを作成します。
表1 サンプルデータ
| 年 | 月 | 日 |
| 2020 | 1 | 15 |
| 2019 | 2 | 10 |
| 1988 | 11 | 29 |
| 2021 | 7 | 8 |
続いて、上述のとおり新規クエリを作成し図1のようにフィールド設定をします。
さて、これで準備は整いましたので実際にデータシートビューでクエリの結果を見てみましょう。
図2に示すとおり、年、月、日の各整数値を用いて「年月日」として日付データに変換することができました。
このように、DateSerial関数を用いることで日付データを生成することができます。この性質を用いて、次から月初及び月末の日付データを作成してみましょう。
月初データの作成方法
まずは、月初データの作成方法を見ていきましょう。先ほど作成したクエリの「年月日」フィールドを次のように修正します。
月初: DateSerial([年],[月],1)
直感的にもイメージしやすいと思いますが、DateSerial関数の「day」引数に「1」を設定し、月初の日付情報が作成されるようにしました。実際のクエリ設定とその結果を図3に示します。
月末データの作成方法
続いて、月末データの作成方法を見ていきます。月初の場合はday引数を「1」にすれば良いだけでしたが、月末の場合は一工夫必要になります。
なぜなら、月末の日にちは30日であったり、31日であったりと様々だからです。このため、DateAdd関数を用いて月末の日にちを導きます。
ここで、DateAdd関数については以下の記事をご参照ください。
DateAdd関数を用いれば、日付データに対して加算減算をすることができます。そこで、次の方法で月末日を算出します。少し回りくどいですが、動的に月末の日にちを求める方法の考え方としては、翌月月初から1日遡った日が当該月末になることを利用します。
そこで、先に作成したクエリに次のフィールドを追加します。
月末: DateAdd(“d”, -1, DateSerial([年], [月]+1, 1))
この式は、まず「DateSerial([年], [月]+1, 1)」で翌月の月初日を求め、続いてDateAdd関数で1日前に戻すという操作をしています。
実際のクエリ設定とその結果を図4に示します。
このように、DateSerial関数とDateAdd関数を用いることで月末日を算出することができるようになりました。
例えば、月の期間集計をしたいといった場合にはBetween句を用いて月初、月末を設定することで効率的に月次データの処理ができるようになりますのでお試しください。
まとめ
今回は、DateSerial関数の使用方法、DateSerial関数の活用例として月初・月末の日にち算出方法を紹介しました。
これらは様々な場面で使う機会があると思いますので、是非使用方法を覚えておいていただければと思います。
以下書籍でもDateSerial関数について取り扱っていますので、ご参考になさってください。特に、クエリやVBAで実装をする際に、これら書籍は辞書代わりにもなって非常に便利なものです。是非お手元に1冊置かれておくと良いと思います。
スポンサーリンク
コメント