SQL: What's negative rounding in SQL Server T-SQL?

I remember being pleased some years back when I finished reading all of SQL Server’s Books Online (now just the documentation pages for T-SQL and SQL Server). The more of those pages I read, the more I was fascinated by small things that I hadn’t noticed even though I’d used the product for a long time. There’s so much to SQL Server and even just to T-SQL, that I still find unexpected things all the time.

Today I was reading an email from Postgres Weekly (yes I follow the dark side too), and it mentioned about what happens with the ROUND function if you pass a negative number to the second parameter i.e. the one that specifies how many decimal place.

Negative??

I don’t know why but it had never dawned on me that I could pass a negative value to that, and sure enough, that works exactly the same way in SQL Server and T-SQL.

If you pass a negative value, you start going back up through the whole number part instead of the decimal part. Here are some examples:

For me, that was both unexpected, and way cool. I can’t imagine why I’d never thought about it before, but that’s going to be useful for me in future. I used to do that type of rounding in far messier ways.

Hope you find it useful too.

2019-03-14