CODEWITHSUNDEEP
sqlsql-servertimetimespan
Only Bolivian Here
Only Bolivian Here

Reputation: 36743

If I wanted to save a span of time to the database, what data type would I use?

I'd like to save the duration of a game. Something like 1:43:00 or 0:32:12.

What data type should I choose if I'm using Microsoft SQL Server 2008 R2?

Upvotes: 4

Views: 1612

Answers (4)

James Johnson
James Johnson

Reputation: 46047

Might be better to store the start and end time of the game, and calculate the duration on the fly as needed. That way, you can change the start and/or end time without having to explicitly update the duration.

As for your question, I would use an int data type and store the duration in minutes. By storing it in minutes, you can easily convert it to hours. The DATEDIFF function already returns an INT, so you wouldn't need to do any additional manipulation.

DECLARE @TotalMinutes INT
SELECT @TotalMinutes = DATEDIFF(MINUTE, StartTime, EndTime)

Upvotes: 1

Joel Coehoorn
Joel Coehoorn

Reputation: 415840

Use an integer type, and just store a quantity for the smallest unit of precision you care about (in this case, seconds). Your client program code should worry about formatting it. So for your two sample values, the stored data would be 6180 and 1932.

To show the values, C# (for example) would use something like TimeSpan.FromSeconds().ToString()

Upvotes: 3

Madison
Madison

Reputation: 411

I would just store the number of seconds. Normally storing two dates would suffice, but I would think that would be something you track in the game itself or per session.

Upvotes: 2

JNK
JNK

Reputation: 65157

Either:

TIME datatype...

or store the start and end times as smalldatetime

I would say store 2 records as smalldatetime so you have the option of going longer than 24 hours without needing to change your data structure.

Upvotes: 2

Related Questions