I was trying (by a mistake) to update bit typed field with non-bit value. It took me some time to realize the cause for not getting this field updated (non-bit value). I was supprised that no native arithmetic overflow exception was raised by the SQL Server query engine.

Does anyone know why is works that way (no exception) for "bit overflow" updates?

On the side I immediately tested updating TINYINT with out-of-range value and it raised an (expected) exception.

I used Microsoft SQL Server 2014 (SP1-CU7) (KB3162659) - 12.0.4459.0 (X64)

  • 5
    According to bit (Transact-SQL), Converting to bit promotes any nonzero value to 1. – Scott Hodgin Aug 28 '18 at 19:28
  • 2
    Honestly I think this was left alone so that people could type true or false into the old "Open Table" dialog in SSMS. Try: SELECT CONVERT(bit, 'true'), CONVERT(bit, 'false');. BIT is kind of half way between a numeric and a boolean. If you want an exception on values outside of 0,1, you could do so with a constraint. – Aaron Bertrand Aug 28 '18 at 19:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.