愚者の経験

「また今度」はほとんどこない

[SQL Server][Access]日時のフィールドで苦労した話

Accessで時刻を入力するのは若干面倒くさい。最終的に使いたい項目が日時だとしても
「日付」と「時刻」の別にフィールドを設けることが多いです。以下長文ごめんなさい。

テキストを1つにすると日付だけ既定値で入れられることが出来たとしても
時刻を続けて入力するにはキャレットを移動させるしかなく、F2キーやマウスをクリックしたり矢印キーを
使用するのを避けられないのが主な理由です。(テキスト毎にEnterイベントでSelStart入れればそんなもんですがそれもちょっと…)
他の理由としては自前のカレンダーや時刻の入力フォームが別にある場合該当のテキストへの入力が複雑になるということもあります。

さらに大きな問題としてOSの時刻の表示が「H:mm」になっているせいで定型入力が「00:00」系で例えば「09:00」
と入力しても再表示時に「9:00」となって、フォーカスすると「9_:00」となって出るときにエラーになって安定しなかったり、
時刻に24時以上を打ち込みたい場合はどうしようもないという理由で最近は時刻は文字列型にしています。
(エラーの方に関しては私が体験したもので必ずでるというわけではないです。今やってみても再現出来ない…。)

そんなこんなでフィールドを2つに割ったとして今度は利用する際に毎回「[日付]+cdate(nz([時刻],”00:00″))=……」とか
しなければいけなくなるのですが、SQLServerを利用している場合は「計算列」という機能があるので
これを使うんですがちょっと問題があります。

24時以上を入力しない場合は計算列の数式に「[日付]+cast(isnull([時刻],’00:00′) as datetime)」とやればいいんですが
「列’xxxx’の数式を検証中にエラーが発生しました。変更を取り消しますか?」と言われてしまいます。
うーんあってるはずなのに…多少書き方を変えても同じです。
どうすればいいかというと上記警告を無視して(上記ダイアログを[いいえ]で突破する)、テーブルの保存時にも似たようなことを
警告されますが再度無視し、テーブルを見ると…ちゃんと出来てますorz
この時castで書いたはずの型変換はconvert(datetime,isnull([時刻],’00:00′))に書き換わっていました。
これで書けばいいのか~とconvertで書いてもやっぱりエラー笑

「悩みまくったのは一体何だったんだ」と思いました。インデックス貼るまでは。
続いてアプリがこなれて仕様も固まってきた頃、速度改善のためにインデックスを作成しようと思って
上記の計算列を選ぶと「インデックスの作成に失敗しました」という趣旨のエラー発生。
「あ~計算列はインデックスキー列に含めないのね」と勝手に思って仕方なく付加列に追加…
出来ません!再度エラーが出ます。
なんで?と思って調べたら、以下の情報がヒット
https://msdn.microsoft.com/ja-jp/library/ms189292.aspx
「決定的」であるかどうかが重要で文字列型を日付にconvertする時は値が決定的になる第三引数が必要だそうです。
なので「convert(datetime,isnull([時刻],’00:00′),108)」とすると保存時にエラーは出ますが、インデックスは使えるようになりました。

うーん。言いたいことは分かるんですがエラー出るのは気持ち悪い。とりあえず24時対応しましたところ
(([日付]+CONVERT([int],left(isnull([時刻],’00:00′),(2)))/(24))+CONVERT([datetime],(CONVERT([varchar](2),CONVERT([int],left(isnull([時刻],’00:00′),(2)))%(24))+’:’)+right(isnull([時刻],’00:00′),(2)),(108)))
これだとエラー出ないんですよね…不思議。

広告

コメントを残す

以下に詳細を記入するか、アイコンをクリックしてログインしてください。

WordPress.com ロゴ

WordPress.com アカウントを使ってコメントしています。 ログアウト /  変更 )

Google+ フォト

Google+ アカウントを使ってコメントしています。 ログアウト /  変更 )

Twitter 画像

Twitter アカウントを使ってコメントしています。 ログアウト /  変更 )

Facebook の写真

Facebook アカウントを使ってコメントしています。 ログアウト /  変更 )

w

%s と連携中

%d人のブロガーが「いいね」をつけました。