新　SQL Server で時間を切り捨てる

今、横浜では 「Microsoft Tech Ed 2010 Japan」 が行われています。
最下層の屑技術者たる僕はそういうのにもいけず、
絶対に不可能な日程の仕事で、
欝が入ってますます効率が落ちている状態です。

そんなわけで逃避行動。

「SQL Server で時間を切り捨てる。」http://10395.diarynote.jp/200606080926140000/

はアクセスが多いです。
　現時点で「sqlserver trunc 日付」でググるとトップに来るし。
まぁ、ほとんどの日本語のページが

「日時から日付だけを取り出す」方法

として

「文字列にして切り取る」

という方法を示しているというある意味憂うべき状態だからなぁ。
・・・とはつつ、SQL Server のDatetime型を float として利用するのも問題が無いとはいえないんだけど。内部の実装に依存していることだから。
もちろん、SQL Server 2008 での基本且つ最良の方法である、

「SQL Server 2008 では date型へキャストする」 SELECT CAST(GETDATE() AS DATE)

というのは結構あるが。

====================================
　余談(本題はその後)
　僕はあたりまえだと思うのだが
「あたりまえ」過ぎるのか、
「どんな場面でも通用する万能のルールではない」のか
あんまし SQL 作るときの注意点として挙げられていないような気がすることとして、

SQL の条件式(WHERE句)で
・インデックスを活かすために列に対して演算やキャスト(暗黙のキャストも含む)は避けておく。
・文字列の比較は数値の比較に比べて遅い。日時型は数値の類型と考えられるので
文字列の比較は日時型に比べて遅い。さらに文字列のparse は遅いので文字列を日時にキャストする処理は遅い。

がある。
(2010年9月8日:「日付型」を「日時型」に訂正。本質的には日付型も同じ話だが下記の例は日時型なので。)
なので、
条件式に

SELECT * from test1 WHERE CONVERT(nvarchar(10),dt1,111) = ’2010/08/27’

SELECT * from test1 WHERE CAST(floor(CAST(dt1 AS FLOAT)) AS DATETIME) = CAST(’2010/8/27’ AS DATETIME)

DECLARE @dt AS DATETIME
SET @dt = CAST(’2010/8/27’ AS DATETIME)
SELECT * from test1 WHERE (dt1 >= @dt) AND (dateadd(day,-1,dt1) < @dt)

とかせずに

SELECT * from test1 WHERE(dt1 >= CAST(’2010/8/27’ AS DATETIME)) AND (dt1 < dateadd(day,1,CAST(’2010/8/27’ AS DATETIME)))

とする。(もちろん、’2010/8/28’にすればいいのだが。)

SELECT * from test1 WHERE dt1 between CAST(’2010/8/27’ AS DATETIME) AND CAST(’2010/8/27 23:59:59.999’ AS DATETIME

は論外。ダメ!絶対!! http://10395.diarynote.jp/201004261843122549/

=========================================================
さて、本題。

「SQLServer Trunc」あたりで日本語以外を含めてググると

「How to truncate a datetime in SQL Server」
http://stackoverflow.com/questions/923295/how-to-truncate-a-datetime-in-sql-server

なるページがひっかかる。

　このページの「The fast way:」(注参照)は「SQL Server で時間を切り捨てる。」http://10395.diarynote.jp/200606080926140000/　と同じ方法だが、
「The correct way:」は僕の知らなかった方法で、且つ「文字列にして切り取り」とも異なる方法であり、日本語のページでは見かけたことが無い方法だった。

SELECT dateadd(DAY,0,DATEDIFF(DAY,0,getdate()))

を実行すると

-----------------------
2010-08-27 00:00:00.000

(1 行処理されました)

となる。

もっとも、この方法、整数を datetime型に暗黙にキャストしているので、float にキャストしているのと大差ないともいえる。

同じサイトで別のページを探すとこんなのがひっかかった。
「Best approach to remove time part of datetime in SQL Server」
http://stackoverflow.com/questions/1177449/best-approach-to-remove-time-part-of-datetime-in-sql-server


似てはいるが、引数の順序が違う。

SELECT dateadd(DAY,DATEDIFF(DAY,0,getdate()),0)

を実行すると

-----------------------
2010-08-27 00:00:00.000

(1 行処理されました)

これも、0 に対する datetime 型への暗黙のCast に依存している。

SELECT CAST(0 as datetime)

で

-----------------------
1900-01-01 00:00:00.000

暗黙のキャストを嫌って変えるとたとえばこうなるか。

DECLARE @dtBase as datetime
SET @dtBase = CAST(’2010/1/1’ as datetime)
SELECT dateadd(DAY,DATEDIFF(DAY,@dtBase,getdate()),@dtBase)

SQL Server 2008 では date型にキャストすればいいので、
こんなことをする必要はないが、
この方法のおもしろいところは
日単位のみではなく、
年月、時、分、秒　でも使えるところである。
(http://stackoverflow.com/questions/85373/floor-a-date-in-sql-server　)

DECLARE @dtBase as datetime
SET @dtBase = CAST(’2010/1/1’ as datetime)
SELECT ’YEAR:’,dateadd(YEAR,DATEDIFF(YEAR,@dtBase,getdate()),@dtBase)
UNION ALL SELECT ’MONTH:’,dateadd(MONTH,DATEDIFF(MONTH,@dtBase,getdate()),@dtBase)
UNION ALL SELECT ’DAY:’,dateadd(DAY,DATEDIFF(DAY,@dtBase,getdate()),@dtBase)
UNION ALL SELECT ’HOUR:’,dateadd(HOUR,DATEDIFF(HOUR,@dtBase,getdate()),@dtBase)
UNION ALL SELECT ’MINUTE:’,dateadd(MINUTE,DATEDIFF(MINUTE,@dtBase,getdate()),@dtBase)
UNION ALL SELECT ’SECOND:’,dateadd(SECOND,DATEDIFF(SECOND,@dtBase,getdate()),@dtBase)
UNION ALL SELECT ’:’,GETDATE()

------- -----------------------
YEAR: 2010-01-01 00:00:00.000
MONTH: 2010-08-01 00:00:00.000
DAY: 2010-08-27 00:00:00.000
HOUR: 2010-08-27 08:00:00.000
MINUTE: 2010-08-27 08:32:00.000
SECOND: 2010-08-27 08:32:23.000
: 2010-08-27 08:32:23.843

特に年月に関しては有用だと思われる。
切り捨てなので、四捨五入(?)は半分引いて切り捨てて1足せばいいかな。
半分足して切り捨てればいい。

DECLARE @dtBase as datetime
SET @dtBase = CAST(’2010/1/1’ as datetime)

DECLARE @dt1 datetime
DECLARE @dt2 datetime
SET @dt1= CAST(’2010/8/27 08:16:29’ as datetime)
SET @dt2= CAST(’2010/8/27 08:16:30’ as datetime)
SELECT DATEADD(MINUTE,DATEDIFF(MINUTE,@dtBase,DATEADD(SECOND,30,@dt1)),@dtBase)
SELECT DATEADD(MINUTE,DATEDIFF(MINUTE,@dtBase,DATEADD(SECOND,30,@dt2)),@dtBase)

ここのサイトは勝手に全角化されたりするので、コードのサンプルは以下からコピーしてください。(2011年2月3日リンク修正)
http://kazamai7610.wordpress.com/2010/08/26/%e3%80%8c%e6%96%b0%e3%80%80sql-server-%e3%81%a7%e6%99%82%e9%96%93%e3%82%92%e5%88%87%e3%82%8a%e6%8d%a8%e3%81%a6%e3%82%8b%e3%80%8d%e3%81%ae%e3%82%b5%e3%83%b3%e3%83%97%e3%83%ab%e3%82%b3%e3%83%bc%e3%83%89/

2011年2月3日追記:
注：
「The fast way:」とあるが、コメントや他のページの情報では「The Correct way:」の方が速いようである。浮動小数点演算よりも整数演算のみの方が速いということらしい。