CODEWITHSUNDEEP
sqlsql-server
Alexandria
Alexandria

Reputation: 183

Converting text to number

I have a online form which a user selects duration of class from a dropdown list. This data then goes into a database field called duration - varchar(5), a data example would be 02:30. Then I run a stored procedure which should convert the data to 2.5 This allows for calculation of duration with hourly rate of pay.

DataFields
Duration - varchar(5) 02:30
ValueOfDuration - numeric(18,2) 2.5

My problem is I am getting an error when this stored procedure runs.
Error converting data type varchar to real.

UPDATE 
ManualAdjustments
SET 
ValueOfDuration = ROUND (CONVERT(real, LEFT(Duration, 2))+(CONVERT(real,RIGHT(Duration, 2)) / 60.0),2) 
WHERE ValueOfDuration IS NULL

Upvotes: 2

Views: 240

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 81970

Assuming SQL Server AND your Duration does not exceed 59:59

Update YourTable Set ValueOfDuration  =  DateDiff(SS,'1900-01-01','1900-01-01 00:'+Duration)/60.

In this case 02:30 would return 2.5

Upvotes: 2

Jaydip Jadhav
Jaydip Jadhav

Reputation: 12309

If Database is SQL Server

UPDATE 
ManualAdjustments
SET 
ValueOfDuration= CAST(DATEDIFF(minute,'1990-1-1','1990-1-1 '+Duration)/60.0 AS NUMERIc(15,2))
WHERE ValueOfDuration IS NULL

Upvotes: 1

Related Questions