SQL Serverの日付シリアル値

SQL Server で日付を扱うとき通常はdatetime型かsmalldatetime型を使うのですが、ごくまれにシリアル値をテーブルに格納するシステムがあったりします。

そういった場合、ちょっと注意が必要です。

そのシステム内だけでデータを扱っているだけなら問題無いですが、データベースからデータを取り出し、別のシステムで参照するとか、Excelにデータをインポートして参照するといった場合に注意が必要です。

以前、私が関わったシステムでも、Management Studioでシリアル値をコンバートして見てみると日付が実際の日付と２日ずれていることがありました。

なんで２日ずれてるのか不思議で、いろいろ調べた結果分かりました。

結果から言うと、基準日が違ったんです。

基準日とは何かというと、日付をデータとして扱う場合、どこかの日付を基準にそこからカウントしていくというやり方を取っているのですが、その“どこかの日付”が基準日です。

そして、この基準日、ソフトウェアによって違うんです！

まず、SQL Serverですが、1900/01/01 午前0:00 を基準日としています。

そしてこの日を「0」として日数をカウントしています。

ためしに2013年7月18日で見てみると。

SELECT CONVERT(float, CONVERT(datetime, '2013/07/18'))

結果は、41471

次に、.NETですが、DateTime.ToOADateで変換する場合、1899/12/30 午前0:00 を「0」としてカウントしています。

つまり、1900/01/01 午前0:00 は「2」です。

var targetDate = new DateTime(2013, 7, 18);

Console.WriteLine(targetDate.ToOADate());

結果は、41473

次に、Excelですが、1900/01/01 午前0:00 を「1」としています。

ただし、1900/02/29をカウントしてしまっているので１日多くなっています。

（注：1900年は閏年ではありません。）

これはバグか！？と思ったのですが違うみたいです。

どうやらExcelを作った際にLotus 1-2-3 のデータをインポート出来るようにこうしてしまったみたいです。

詳しいことはここらへんを見てみてください。

セルにDATE()関数を入れて、表示形式を標準にしたものです。

結果は、41473

この結果を見ると、.NETとExcelは同じ値になります。ただし、1900/03/01以降に限りますが。

そして、SQL Server と.NET、Excelでは２日違うことになります。

なぜ、SQL Serverが1900/01/01を「0」として基準日にしたのか？

もしかしたら元々SQL Serverがマイクロソフトの製品じゃなかったからか！？

実際のところはよく分かりませんが、全部統一してほしかったです。

まあ、とにかく注意が必要ということです。

頭の片隅にでもメモっておくといいかもしれません。