Posted by Erland Sommarskog on 10/01/67 11:38

serge (sergea@nospam.ehmail.com) writes:
> What would be the best way to store hours/minutes
> based on how we are using Time in the database?
>
> Either I will stick with Integer but store in minutes
> time instead of calculating in seconds and most likely
> update all the SUM(ENTEREDTIME) to
> SUM(CAST(ENTEREDTIME AS BIGINT))

Since you appear to have an application that is working, I would do
as little change as possible.

Storing elapsed time as minutes rather than seconds makes sense if you are
not supporting seconds anyway. Then again, do the potential seconds
cause any menace?

Of course, if you store by minutes you may not need bigint, but that
depends on what that sum(enteredtime) reflects. If you store by minutes,
you need 4085 years for an overflow, which is a lot for a single person. But
if the sum encompasses a workforce of 20000 employeess, it only takes
74 days per person to overflow.

Had we been talking about a new application, I can't say for sure what
I would recommend. All the datatypes have their tradeoffs. But keep in
mind that it's possible to have computed columns, so you could have
both a smalldatetime column and a minutes column if you like.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]