Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
vish2success
Starting Member
1 Post |
 Posted - 2010-01-28 : 09:30:27
|
| I HAVE STRING COLUMN SAY "TIME_USED" WITH 'HOURS:MINS:SECONDS' (eg 153:29:41) FORMATI NEED TO CALCULATE IT TYPICALLY AND STORE IT IN SOME OTHER VARIABLE PLEASE CHECK;--> (MINS.SECONDS)/60 ie (29.41)/60=0.4901and concatinate this value to HOURS ie 153.4901how to do this, i cant use like operator and get the data and do in one go for sql, can any one help me outRegardsNaren |
|
|
DP978
Constraint Violating Yak Guru
269 Posts |
Posted - 2010-01-28 : 09:46:57
|
| Just as an FYI 29:41 != 29.41. If you want decimal form of that you need to do ( 29 + (41/60) ) / 602 things people will tell you...1. Your data better be consistent.2. Try to avoid formatting Datetimes as anything BUT datetimes, they act funky, sort wrong, and typically produce bad answers.Also if you must do what you want you are going to need a few functions...1st: CharIndex to find the first ':'2nd: Reverse & CharIndex to find the 2nd ':'3rd: Cast to change the numbers between the ':' to integers and also to change the trailing digits to integers |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2010-01-28 : 10:09:40
|
| Why do you want to do this?declare @time varchar(10)set @time='153:29:41'select substring(@time,1,charindex(':',@time)-1)+'.'+cast(replace(substring(@time,charindex(':',@time)+1,len(@time)),':','.') as decimal(12,2))/60MadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|
|
how to calculate time part