SQLServerから合計時間を計算しているのですが、計算結果にコロンを入れてDBに保存したいです。

前提・実現したいこと

DBにテーブルAとテーブルBがあり、
テーブルAには日付ごとの単体時間
テーブルBには合計時間をそれぞれ保存したいです。

現在、テーブルA,テーブルBそれぞれ値は取れているのですが、テーブルBに合計時間を保存する際にコロンを入れた状態で保存したいのですが、その方法がわかりません。

単体時間、合計時間それぞれ型はNvarchar(MAX)です。

発生している問題・エラーメッセージ

テーブルB
現在：1000の状態で保存されています。
→実現したい保存方法：10:00

該当のソースコード

vb
1UPDATE Goukei SET Goukei.TGNGSZIKAN = (select(SUM(CONVERT(int,LEFT(TGNSTIME,2)))+SUM(CONVERT(int,RIGHT(TGNSTIME,2)))/60)*100+ SUM(CONVERT(int,RIGHT(TGNSTIME,2)))% 60 FROM Tantai WHERE Goukei.TGNGNNO = Tantai.TGNGNNO) From Tantai WHERE Goukei.TGNGNNO = Tantai.TGNGNNO

試したこと

補足情報（FW/ツールのバージョンなど）

VisualStudio2019,SQLServer

YAmaGNZ

2020/08/11 07:24

SQLを見る限り、計算するときに2桁ずつ分解しているようですから、同じように分解して、":"を入れればいいのではないですか？

退会済みユーザー

2020/08/11 07:27

上記のソースの後でさらにUpdateでコロンを入れるという認識でよろしいでしょうか？

YAmaGNZ

2020/08/11 07:34

下二桁が分で、それより上の桁が時間なんですよね？そうなのであれば、分解してというより計算した値に対して60で割った商と余りを":"で連結する感じですかね？

退会済みユーザー

2020/08/11 07:41

VB のタグがついてますが VB.NET の話では？であれば、タグを訂正願います。 SQL Server のデータ型を nvarchar ではなく、例えば datetime 型にした方がいろいろスムーズに行くと思うのですが、そうしない理由は何かあるのでしょうか？

退会済みユーザー

2020/08/11 07:48

datetimeを使用する場合はどのようにすればよいでしょうか？ Datetimeにして行ったのですが、日付が入ってしまいます。

退会済みユーザー

2020/08/11 07:51

日付に合計時間が入ってしまい、よくわからない数値になってしまいます。

gentaro

2020/08/11 08:17

日付がいらないならふつうにtime型とか使えば良いんじゃないの。

退会済みユーザー

2020/08/11 08:20

time型を使用したところ、intはtimeと互換性がありません。とのメッセージが表示されます。

YAmaGNZ

2020/08/11 08:22

時間の合計を入れたいってことですから、Time型だと24時間超えるような値は入らないのではないですか？

gentaro

2020/08/11 08:29

？？？単純に文字列で「10:00」を保存したいなら、その保存先はtime型で良いんじゃないの、という意味なんだけど。やろうとしてることがやくわかんないけど、そもそもテーブルAとテーブルBの定義がどうなっているの？ > 現在：1000の状態で保存されています。 > →実現したい保存方法：10:00 って書いてあるからそれぞれのテーブルには文字列で1000って入ってて、それが10:00を意味するという解釈で合ってる？「合計時間」とか言うと「計1000時間」という意味にも取れるけど。

YAmaGNZ

2020/08/11 08:34

「合計時間」と書いてあったから、挙げられている例は10:00と10時間を示す値だけど、24時間超える可能性があるのでは？って思っただけなんで、そういう考慮が必要ないって考えなら無視してください。

退会済みユーザー

2020/08/11 08:36

24時間を超える可能性も存在します！その考慮は必要です！

YAmaGNZ

2020/08/11 08:42

最小単位の値（分なのであれば68分とか、秒であれば4080秒とか）として数字(bigint等)で保存すれば一番簡単なのではないですか？合計もただ足すだけだし。それで、表示側で加工して"10:00"といった表記にすればいいのではないでしょうか？

退会済みユーザー

2020/08/11 09:31

> テーブルAには日付ごとの単体時間 > テーブルBには合計時間をそれぞれ保存したいです。「日付ごとの単体時間」とか「合計時間」とか何なのかさっぱりわかりません。ここに書いてないことは知る由もない第三者が読んで、特にエスパーではない自分が読んで、誤解なく分かるように具体的に、あくまで具体的に書いていただけませんか？

退会済みユーザー

2020/08/11 12:54

24 時間を超える場合は以下の記事の「TimeSpanはTicksにしてbigintで保持」というのが良いのかもしれません。 .net, sqlserverで時間を扱う時のTipsとか https://toxn.hatenablog.com/entry/2017/04/12/235137 What is the correct SQL type to store a .Net Timespan with values > 24:00:00? https://stackoverflow.com/questions/8503825/what-is-the-correct-sql-type-to-store-a-net-timespan-with-values-240000

退会済みユーザー

2020/08/12 23:36

非常にわかりにくい質問でご迷惑をおかけしました。。皆さんのおかげで解決することが出来ました！本当にありがとうございます！

行動規範の内容に同意します

回答1件

ベストアンサー

書式の指定にはFormat()が便利です。
※以下は見易くする為に整形しています。

SQL
1UPDATE Goukei SET 
2TGNGSZIKAN = (
3    select format((higaval + lowval / 60) * 100 + lowval % 60, '00:00')
4    from (
5        select SUM(CONVERT(int,LEFT(TGNSTIME,2))) as highval
6             , SUM(CONVERT(int,RIGHT(TGNSTIME,2))) as lowval
7        From Tantai 
8        WHERE TGNGNNO = Goukei.TGNGNNO
9    ) step1
10)