WHERE句にて「30日前から今日まで」の設定方法

質問

WHERE句にて「30日前から今日まで」の設定方法
今まで、下記の通り記述していました。

WHERE M_DATE >= '2012-03-21 00:00:00.000' AND
M_DATE <= '2012-04-20 23:59:59.000'

日付部分を毎回変更するのが面倒なので、エクセルのTODAY関数のようなものがないかと探したところ、GETDATE関数を見つけ、下記の通り変更してみました。

WHERE M_DATE >= dateadd(d,-30, getdate()) AND
M_DATE <= getdate()

しかし、時間部分が現在の時間になってしまうのですね。「今日まで」部分は、現在時刻で問題ないのですが、「30日前から」の部分は、0時0分0秒としたいのです。

どうしたらいいでしょうか？
よろしくお願いします。

回答No.2

Tom-3 · ベストアンサー

テストのために以下の項目を持つ「テーブル:日付テスト」を作成し、実証してみました。
　1.[インデックス] ⇒数値型
　2.[日付] ⇒日付型
データには
2012-03-01 00:00:00.000 ～ 2012-05-01 00:00:00.000
までの日付の値を持つデータを追加しました。

質問されている３０日前までのデータを抽出するという条件を満たす条件としては
(本日=2012/04/23)として、範囲は

2012-03-24 00:00:00.000 ～ 2012-04-23 23:59:59.999

までとなります。

ですが、実際に重要なのは日付のみであり、時間(hh:mm:ss)は必要ありません。
そこでまずは「項目:[日付]」を「YYYYMMDD」の数値型に変更する事を考えます。
その際の記述としては

　CAST(CONVERT(VARCHAR,[日付],112) as BIGINT)

となります。上記の記載でどのように値を変更しているかというと、

１．CONVERT(VARCHAR,[日付],112)で
　　日付型(例:2012-03-01 00:00:00.000)を文字列型(例:20120301)に変換しています。

２．さらに大小比較を正確に行うために文字列型を数値型(BIGINT)に変換するために
　　CASTを行っています。
　　CAST(CONVERT(VARCHAR,[日付],112) as BIGINT)
　　これで「文字列:20120301」を「数値型:20120301」とすることができます。

次に変換した数値型の日付に対して

2012/03/24<=CAST(CONVERT(VARCHAR,[日付],112) as BIGINT)<=2012/04/23

とすれば、2012/03/24ならば何時であっても抽出範囲に入り、また、2012/04/23ならば何時であっても抽出範囲に入ります。

よって、次に本日日付を「数値型:YYYYMMDD」に変換する事と
３０日前の日付を「数値型:YYYYMMDD」に変換し、その条件をWHERE文に組み込めば完成です。

まずは本日日付を「数値型:YYYYMMDD」にする方法としては

　CAST(CONVERT(VARCHAR,getdate(),112) as BIGINT)

となります。
上記の記載は[日付]を「日付取得関数:getdate()」に変更しただけです。
よって、これで「数値型:20120423」となります。

最後に30日前の日付を「数値型:YYYYMMDD」にする方法としては

　CAST(CONVERT(VARCHAR,DATEADD(day ,-30 , getdate()),112) as BIGINT)

となります。
こ部分に関しては質問者様が記載されている

　dateadd(d,-30, getdate())

を組み込んだだけですのでご理解いただけると思います。

以上の構文をクエリに組み込みSELECT文を作成すると以下のようになります。

SELECT
　　[インデックス]
　,[日付]
　,CAST(CONVERT(VARCHAR,[日付],112) as BIGINT)
FROM　[dbo].[日付テスト]
WHERE　CAST(CONVERT(VARCHAR,[日付],112) as BIGINT)>=
　　　 CAST(CONVERT(VARCHAR,DATEADD(day ,-30 , getdate()),112) as BIGINT)
　　AND　CAST(CONVERT(VARCHAR,[日付],112) as BIGINT)<=
　　　　 CAST(CONVERT(VARCHAR,getdate(),112) as BIGINT)

一応検証を取りましたので問題ないと思います。
なお、先頭の空白は見やすくするために全角スペースにしています。
クエリとして使用する際はお手数ですが、半角スペースに変更してください。

お礼

ありがとうございます。
説明もとても丁寧で分かりやすかったですし、わざわざ私なんかのために検証までしてくださったのですね、恐悦至極にございます。ありがとうございました。

回答No.1

Siegrune

convert(char(23),convert(datetime,convert(char(8),
dateadd(d,-30, getdate()),112),112),121)
と比較してみては？

※１ convert(char(8),getdate(),112)
で、'20120321'にして、

※２ convert(datetime,※１,112)
で日付型に戻す。
M_DATE が日付型ならこれで比較すればいいでしょうけど。

※３ convert(char(23),※２,121)
で'2012-03-21 00:00:00.000'の形の文字列に戻す。

（未検証ですが。）

お礼

速やかなご回答、誠にありがとうございました。
上記の方のやり方でうまくいきましたが、convert関数やcharについては、Siegruneさんに教えていただいてから、調べてみていたので、上記の方法もすんなり理解できました。
本当にありがとうございました！