Alexandria Alexandria - 2 months ago 20
SQL Question

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

Answer

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