- PR -

SQL文についての質問（日付型）

1
投稿者投稿内容
まさお
常連さん
会議室デビュー日: 2007/11/06
投稿数: 38
投稿日時: 2008-02-23 00:52
お世話になります。
SQLSeverにて

ありえない日付の文字列、例えば2008/04/31をCONVERT関数で日付型に変換した場合、
「CHAR データ型から DATETIME データ型への変換の結果が日付/時刻の値の範囲外です。」
とエラーが出ます。

これをエラーが出ないようにSQL上で（2008/04/31→2008/04/30）変換する方法ってありますか？

したいことは、
以下のような２つのテーブルがあり、各顧客ごとに締日で請求金額を集計したくて、
(15日締めなら、4月分は3/16〜4/15分を集計し、31日締めならば、4/1〜4/30）
月末が締日である顧客は、テーブルの締日が31日の為31日までない月の場合、
SQLで集計しようとしたらエラーがでてしまい困っています。
どなたかご教授いただけませんか？
宜しくお願いします。

[締日テーブル]
顧客ID | 締日
1, 31
2, 15
3, 10
…
[請求テーブル]
顧客ID | 日付 | 請求金額
1, 2008/04/01, 10000
2, 2008/04/01, 20000
1, 2008/04/02, 12000
…

saki1208
ベテラン
会議室デビュー日: 2006/08/22
投稿数: 86
投稿日時: 2008-02-23 02:58
saki1208です。

バージョンの記載がないですが...

月末の日付を求める場合よく使用する方法として「翌月の
1日からマイナス1日する」といったことが行われます。

月末の日付は月によって異なりますが、1日は必ず1日です。
また、月末は必ず1日の前の日です。

DateAdd のリファレンスを参照してみれば、何か掴めると思います。

# ORACLE ならLastDay関数使用で事足りるのですが...

indigo-x
大ベテラン
会議室デビュー日: 2008/02/21
投稿数: 207
お住まい・勤務地: 太陽の塔近く
投稿日時: 2008-02-23 07:34
（ちょっと厳しい設計ですね）

それはそれとして、考えてみたんですが。。。

CONVERTで変換する必要ないです。単純に文字列で比較すればOKです。

（注意点としては0パディングされていることが必須です）
よっし〜。
ベテラン
会議室デビュー日: 2007/04/17
投稿数: 89
お住まい・勤務地: 北のほうの国
投稿日時: 2008-02-23 11:10
＃アプリケーション側での制御も検討していいかと思いますが
その辺は記載されていないので、SQLServerのみで処理するとして

2008/4/31 で SQLServer にいきなり渡すのは無理があります。

ISDATE などで日付として扱えるかの判断もできますが、
その後の処理が現実的ではありません。
（どうやって 4/30 に持っていくかが面倒です）

一日から月末の集計範囲であれば
引用:

[日付] BETWEEN '開始日' AND 「（開始日の１か月後）の一日前」

で対応可能でしょう。
「（開始日の１か月後）の一日前」
はTransact-SQLのDATEADD関数をご参考ください。

開始日の指定が'2007-3-16'、'2007/3/16'の書式であれば 0パディングは必要なありませんが、'20070316' のような指定であれば、indigo-x さんが言うように0パディングが必須です。
まさお
常連さん
会議室デビュー日: 2007/11/06
投稿数: 38
投稿日時: 2008-02-23 11:21
SQL文上で集計する日付を作成しています。(DATEADD関数を用いて）

イメージとしては、
[開始日付]
請求テーブル.日付 <= (2008/2/ + 締日テーブル.締日)
[終了日付]
請求テーブル.日付 >= DATEADD(DD,1,DATEADD(M,-1,(2008/2/ + 締日テーブル.締日)))
終了日付は締日の日付から１ヶ月前の日付を求めて、さらに１日後にしています。

これを各レコードに対してやっているのでうまくいきません。

文字列比較では終了日付を求めることはできなし、

締日が31日の場合のみ「翌月の１日からマイナス１日する」といったことができません。

浅い知識でやっているので申し訳ないです。。。

indigo-x
大ベテラン
会議室デビュー日: 2008/02/21
投稿数: 207
お住まい・勤務地: 太陽の塔近く
投稿日時: 2008-02-23 11:51
説明が悪かったかもしれませんが

文字列ですれば終了日は'2007/04/99'以下等にすれば問題ないです。

抽象的に言えば、時系列に並んでいるのであれば、区間は求まるはずです。

参考になればと思います。
saki1208
ベテラン
会議室デビュー日: 2006/08/22
投稿数: 86
投稿日時: 2008-02-23 12:17
saki1208です。

私も含め数人の方がアドバイスしている
内容に対して、どうすれば実現できるの
か考えてみてください。

厳しいことを言うようですが現在の貴方
の姿勢ではどんなアドバイスも無駄にな
ります。

PGもSEも仕事の大半は頭を使って考える
ことです。

ちなみに、CASE式はご存知でしょうか?

[ メッセージ編集済み 編集者: saki1208 編集日時 2008-02-23 12:23 ]
よっし〜。
ベテラン
会議室デビュー日: 2007/04/17
投稿数: 89
お住まい・勤務地: 北のほうの国
投稿日時: 2008-02-23 17:20
発想を柔軟に
開始日〜終了日の部分をいかにうまく表現するかです。

コード:
declare @日付 varchar(10)
declare @締日 varchar(2)
declare @数 int
set @数=0
while @数 <= 31
BEGIN
	set @数=@数+1
	set @締日 = @数--'31'
	set @日付 = '2008-2-' + convert(varchar,(case when @締日 >= 28 then '1' else @締日+1 end))
	print convert(varchar,DATEADD(mm,-1,@日付)) + '〜' + convert(Varchar,DATEADD(dd,-1,@日付))
	continue
END
1

スキルアップ／キャリアアップ（JOB@IT）