June 5, 2003 at 10:53 am
I have a field that is declared as a varchar that is suppose to hold time spent working on a project. The field is filled with data like 05:00:00 or 36:00:00. I have been requested to add up those hours and place them into a field that is declared as a decimal data type. I have tried using the CAST function to convert it to a decimal but it has an error converting the format. Is there any way to convert the field into a format that I can run calculations on.
June 5, 2003 at 11:32 am
Does the time represent hours? ie: 05:00:00 = 5 hours
Will there be minutes and seconds?
I would add the varchar data as it is:
05:00:00 plus 36:00:00 equals 41:00:00
then divide it by 24 and keep the remainder.
41 divided by 24 equals 1.7
Save the result as a decimal datatype.
The reason you can't convert as is, is because the colons (:) are not valid in decimal datatype.
-SQLBill
June 5, 2003 at 11:46 am
Based on the assumption that the data is in the format of hh:mm:ss and you only want to add up the hh values :
Select Convert(Int,Substring(FieldName,1,2))
from Table
gives the hh values converted to integer values and the sum of all these can be obtained by :
Select Sum(Convert(Int,Substring(FieldName,1,2)))
from Table
HTH
p.s : SQLBill -- I didn't get the "add the varchar data as it is" part...I assume you meant add using the "+" sign...and for varchar values it just concatenates the results??...am I missing something out here....???
June 9, 2003 at 10:12 am
winash,
No, you weren't missing anything. I made a mistake.
-SQLBill
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply