I am inserting values form a staging table in SQL Azure into a production table. Values in the staging table are varchar, while the target value in the production table is tinyint (it is an age field for humans, so I reckon that up to 255 will suffice ;) )

Problem is that due to a bug in the source system, we tend to get some values as -1 or -2 when they should be 0. This causes issues, since TINYINT only supports values form 0 to 255 (and also because this is factually wrong). I was wondering if there is some sort of case statement that I could use to convert values to 0 if they fall under 0, or perhaps if they fall outside the 0-255 range?

2 Answers 2


As TINYINT only allows 0-255 you could also use


The TRY_CAST above returns NULL if supplied an invalid input for the datatype (including -ve numbers) and I would be minded to leave it that way.

But if you must change these to 0 you can wrap it in an ISNULL


Try something like this

DECLARE @BadData VARCHAR(100) = '-5'

SET @TinyInt = CASE 
        WHEN cast(@BadData AS INT) < 0
            THEN 0
        ELSE @BadData

  • This seems to work! SELECT CASE WHEN cast([fieldname] AS INT) < 0 THEN 0 ELSE [fieldname] END
    – pmdci
    Sep 21, 2016 at 11:40
  • Naturally, the result of the CAST still has to be <= 255 or it won't fit into a TinyInt (you'll get an arithmetic overflow) Sep 21, 2016 at 11:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.