Inserting Numeric and Text data in VARCHAR field

  • I need to identify cases where a job runs over its estimated completion time. Code to recreate:

    DECLARE @Table TABLE (
                         RowNum INT
                        , ExpectedJobTime DECIMAL(12,2)
                        , LoggedJobTime DECIMAL(12,2)
                        , RemainingJobTime VARCHAR(24)
                         )

    INSERT INTO @Table (RowNum,ExpectedJobTime,LoggedJobTime)

    VALUES

    --(1,14.7,10.6) -- Positive value works
    (1,5.4,6.73) -- Negative value doesn't work

    UPDATE @Table
    SET RemainingJobTime = CASE
                                WHEN LoggedJobTime > ExpectedJobTime
                                    THEN 'Standard Exceeded'     
                                ELSE ExpectedJobTime - LoggedJobTime
                            END

    SELECT *
    FROM @Table

    If the value of LoggedJobTime is greater than that of ExpectedJobTime then RemainingJobTime should be set to 'Standard Exceeded' otherwise it should be the result of ExpectedJobTime - LoggedJobTime. It works in cases where the result of ExpectedJobTime - LoggedJobTime is positive but fails with 'Error converting data type varchar to numeric.' when that result would be negative. I'm clearly going about this wrong here but am not sure how to correct it. Can anyone show me the proper way to do this? Thanks in advance for looking at it.

  • The remainingJobtime field is varchar
    So you have to convert number into string

    Try this


    UPDATE #table SET
    RemainingJobTime = CASE THEN LoggedJobTime > ExpectedJobTime  THEN 'Standard Exceeded'    

    ELSE

    CAST(ExpectedJobTime - LoggedJobTime AS VARCHAR(10))

    END

    FROM #table AS t

  • That does the trick. Thanks!

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply