Re: What is the right datatype to store Hours up to the minute precision?
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Sat, 28 Jan 2006 17:12:47 +0000 (UTC)
serge (sergea@xxxxxxxxxxxxxxxxx) 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@xxxxxxxxxxxxx
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
.
- References:
- Prev by Date: Re: MS Access Frontend for remote sql server
- Next by Date: Re: SQL Server 2000 query time out option, what does it do?
- Previous by thread: What is the right datatype to store Hours up to the minute precision?
- Next by thread: How to find first not null value in column
- Index(es):
Relevant Pages
|
