SQLでの日付データの使い方

日付データの扱いをメモします。なにしろSQLServer2005にはDate型がないので日付データを扱うときはいろいろ気を使います。2015/11/26, 12/20追記。9/5追記。

日付データはDateTime型の列に登録する

日付データの扱いでは、たぶん、これが一番重要。varcharとかintの列に日付を登録していたソフトをいくつか見たことがあるけど、素直にDateTimeを使いましょう。
SQLServer2008移行を使っているなら、Date型が使えます。もう切り替えすべき。

• datetime型　2017/9/5

• int型　20170905　　これはNG
• varchar '2017/09/05'　　これもNG

日付データの検索での注意点

datetimeには年月日にあわせて時分秒のデータもあるので、たとえば、「2014/7/7から2014/8/8」までという検索をしようとしたら、

select * from table where [日付] between '2017/7/7' and '2014/8/8' 

ではよろしくない。「2014/8/8」だけだと「2014/8/8 0:00:00」と解釈されるので、この例だと、2014/8/8 21:00:00 というデータが抜けてしまう。 時分秒も付けて、

select * from table where [日付] between '2014/7/7' and '2014/8/8 23:59:59'

とすればほぼ大丈夫だが、23時59分59秒50ミリ秒のデータはもれてしまう。 betweenを使うのはあきらめて

select * from table where '2014/7/7' <= [日付] and [日付] < '2014/8/9'

と書くのがよいかも。なにか負けた気がするが。

datetime型のデータを「yyyy/mm/dd」形式のテキストに変換する方法

convert関数を使うと簡単にできます。

select convert(varchar, [日時], 111) from table

謎の数字の「111」は日本の標準形式のテキストらしい。
CAST および CONVERT (Transact-SQL)

日付から年度を得る手順

３か月ずらして年を取る。DateTime型をつかうと、こういう便利なことができる。たとえば、「2017/2/3」が2016年度（年度は４月から翌年3月末まで）というのは、まず3か月引いて「2016/11/2」にしてから年を得ればよい。

year(dateadd(mm, -3, 実施年月日))

1月31日の1ヵ月後は？

１月末日の１ヵ月後なので、２月２８日（閏年なら２９日）がほしい所。これもDateAdd関数を使うとやってくれる。このあたりは日付型を使っている利点。int型で「20150131」というデータを持っていると、こんな計算は無理です。

dateadd(mm, 1, '2015/1/31') -> 2015/2/28
dateadd(mm, 1, '2016/1/31') -> 2016/2/29 　（うるう年）

（2017/7/19 追記）datediffは年齢の計算には使えない

datediff 関数は日付型の差を計算してくれるが、年齢の計算には使えないので注意。まさかの衝撃の事実。 1970年７月生まれの人が2020年に何歳かを計算する例だと、誕生日の前でも後でも50歳になってしまう。 

datediff(yy, '1970/7/7', '2020/6/30') -> 50  （本当は49が欲しい）
datediff(yy, '1970/7/7', '2020/8/31') -> 50 

月数をカウントして12で割ればなんとかなりそうだが、無理やり感が強いし、これ、誕生日の前日だとだめだし。

datediff(MM, '1970/7/7', '2020/6/30') / 12 -> 49
datediff(MM, '1970/7/7', '2020/8/31') / 12 -> 50

datediff(MM, '1970/7/7', '2020/7/6') / 12 -> 50  （本当は49が欲しい）
datediff(MM, '1970/7/7', '2020/7/8') / 12 -> 50 

残念。 年齢を計算するには、今のところ、日付を8桁の整数に変換して差をとって10000で割るという手しか思いつかない。なんか、敗北感が大きい。

 (20200719 - (year(生年月日) * 10000 + month(生年月日) * 100 + day(生年月日)) / 10000 

日付データを数値で扱うとうるう年を正しく扱えないことが多いのだが、この場合も「2月29日」生まれの人は3月1日にならないと歳をとらない（それはいいのか）。

＊

法律では、誕生日の前日の24時に歳をとることになっていたのだっけ、、、いわゆる4月1日生まれの人は3月31日に歳をとるという件。いろいろと面倒そう。

日付データを引き算すれば年齢がわかる(2018/6/15追記)

SQLを日付データを引き算すれば年齢がわかるのでは？とひらめいた。

試してみると、1900年が余分だった。

year(cast('2020/6/30' as datetime) - cast('1970/7/7' as datetime))  →1949

つまり1900を引けばよいと云うことで、

year(cast('2020/6/30' as datetime) - cast('1970/7/7' as datetime)) - 1900  →49
year(cast('2020/8/31' as datetime) - cast('1970/7/7' as datetime)) - 1900  →50

おお、ちゃんと年齢が計算できた。SQLでの年齢の計算法はこれで決まりだね。こんな感じで使える。

year(getdate() - '1970/7/7') - 1900　←1970/7/7生まれの年齢は
year(getdate() - [生年月日]) - 1900   ←生年月日の列があるなら

1900を引いている意味が分からんとか云われそうな気もするが、、、日付型の引き算の型は日付型で、日付型のデータは1900年1月1日0時が起点だから、と説明すればわかるよね。