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.

Recommended Answers

All 3 Replies

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
commented: that will work nicely. +8

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

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

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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.