Best data type for storing "Hours"
-
I thought I might ask other's opinions on what the best data type is for storing Hours. In the past I have tried Float, Decimal, Numeric and Real data types. However, I've ALWAYS had problems with these types of data because it would NEVER store the amounts exactly as I had put in. For example, insert 2.5 in the Column resulted in 2.4999999999.
I finally settled on the SmallMoney data type. It was small enough and used the same number of bytes that the other data types used. The reason was, it NEVER messed up what I tried to store in the field. If I would insert 2.5, I got back...2.5!
To this day I have never had a problem calculating sums or anything. The only thing was that sometimes Crystal Reports would assume it was money and not Hours, but that was very easy to remedy.
Anyway, does anyone else have different thoughts on this? Is using a SmallMoney for the data type wrong? Immoral?
-
Firstly, whatever works.
However using money or smallmoney your output will always be to 4 decimal places, that is the nature of the data type.
Personally I would use numeric (aka decimal) and define it to hold the maximum value possible with the appropriate scale (1 or 2 decimal places). There is no ambiguity in data type (why are you storing non monatory value as money?) and SQL will use only the number of bytes needed to store the maximum value.
Far away is close at hand in the images of elsewhere.
Anon. -
Ok, I guess I never took the time to look into the Precision / Scale of Decimals. It seems like that would work. Could you elaborate a little on Precision and Scale for me? It's a bit obscure in the BOL.
The fact that SmallMoney = 4 decimal places doesn't bother me, those do not show up programatically, in reports, or even in Query Analyzer. However, I guess I really only need 2 decimal places. What would the optimum Decimal configuration be in this case? Right now I've tried Precision = 18, Scale = 2.
-
From BOL:
p (precision)
Specifies the maximum total number of decimal digits that can be stored, both to the left and to the right of the decimal point. The precision must be a value from 1 through the maximum precision. The maximum precision is 38. The default precision is 18.
s (scale)
Specifies the maximum number of decimal digits that can be stored to the right of the decimal point. Scale must be a value from 0 through p. Scale can be specified only if precision is specified. The default scale is 0; therefore, 0 <= s <= p. Maximum storage sizes vary, based on the precision.
Precision Storage bytes
1 - 9 5
10-19 9
20-28 13
29-38 17
So if the maximum value you wish to store is 99999.99 then the precision would be 7 and scale 2 and would take up 5 bytes, the same amount for precision 9, scale 2, max 9999999.99.
Precision 9, scale 0, takes up 5 bytes but can hold max of 999999999, no decimal places.
Hope this helps
Far away is close at hand in the images of elsewhere.
Anon. -
as long as no one
will use your "hours"-column for timecalculations, all workarounds may be fine.If you want to perform timecalculations, be more strict regarding datatype-determination and use datatime or smalldatetime. You might even consider to always have the date-part set to 1900-01-01.
Functions like datediff, dateadd, ... are allready in place to help with calculations.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Hmm, well the Decimal data type is still larger than SmallMoney (although I'm really not concerned about 1 Byte
). SmallMoney comes in at around 4 bytes where as the lowest I can see (that won't give me errors entering data) is about 5 bytes. Is that correct or am I reading something wrong?Also, why on earth would I do Time Calculations on an Hours Column when I have Start / End Time Columns? Even doing DateDiffs (etc) always comes out to the same amount in the Hours Column (which is calculated any inside of a Stored Proc). The only reason that there IS an Hours Column (actually there is Hours, ActualHours, and OverTime) is for ease of User Interfaces / Reports and for calculating Actual Hours / OverTime.
So I guess at this point I'm not convinced switching to a Decimal will be advantageous. Any thoughts?
-
Smallmoney is 4 bytes and has a maximum of 214,748.3647
To hold the equivalent in decimal would be 9 bytes decimal(10,4)
However if you are only storing to 2 decimal places then
smallmoney would still be 4 bytes and a maximum of 214,748.3647
but numeric(9,2) is 5 bytes with a maximum of 9,999,999.99
It depends if you are worried about the extra byte or not.
If smallmoney works then use it but it would be wise to make the column name clear so that another person does not get confused and think the column is a monetary value.
Far away is close at hand in the images of elsewhere.
Anon. -
In my solution I needed hours recorded with one decimal place only. I decided to go with the datatype smallint. I store everything in the db multiplied by 10. No problems with rounding and no waste of space. (In fact, 255 > 240 which is my 24.0 hours, so you could even get away with using tinyint.)
Of course in the app side I have to do the conversion back and forth, but not a big deal.
I even made user-defined datatype called "hourX10" Be carefull when adding two tinyints. You must cast to a large enough type of you could get an overflow.
If you need to record to the minute, another approach might be to store by minutes. Can still store this conveniently in a integer datatype.
Greg
-
Ok, thanks for all the input, I'll look into the Decimal data type more and make a decision.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply