0

Okay, I have a table called InGameTime with the following columns:
CharName varchar(30)
InTime datetime
OutTime datetime
HH int
MM int
SS int


I am trying to make a trigger, that will once data is updated, it will update the HH,MM,SS to add the total hours, minutes, seconds spent in game. I can get as far as making it add everything, but it adds everything entirely, I want it to be capped off at 60 Minutes, 3600 (I believe?) Seconds. So when the minute column reaches 60, it would roll back over to 0 and set Hour +1 etc...

I can try to explain more if needed, and thank you for your time.

3
Contributors
3
Replies
4
Views
6 Years
Discussion Span
Last Post by Silent-Regret
2

Try modifying the following to pull from the table, rather than comparing the date variables:

DECLARE @intime DATETIME
DECLARE @outime DATETIME
DECLARE @time int--total seconds
DECLARE @days int, @hours tinyint, @minutes tinyint, @seconds tinyint
SET @intime = GETDATE()
SET @outime = DATEADD(S, 3663, @intime)

SET @time = DATEDIFF(S,@intime,@outime)
SET @days = @time / 86400
SET @hours = (@time/3600) - (@days * 24)
SET @minutes = (@time/60) - (@days * 1440) - (@hours * 60)
SET @seconds = @time % 60

SELECT @time total_second_count
	 , @days day_count
	 , @hours hour_count
	 , @minutes minute_count 
	 , @seconds second_count
Votes + Comments
that will work nicely.
0

No need to store data in HH, MM, SS columns.

These can be calculated at run time using the previous solution.

0

Sorry for the long response, and thank you for the answers.

I was trying to make this so that I could pull the 3 columns for a PHP script on our website, to show the total time logged in.

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.