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?
Best Answer
As
TINYINT
only allows0-255
you could also useThe
TRY_CAST
above returnsNULL
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 anISNULL