You have some REALLY big numbers in your T-SQL statement, how do tell the millions from the thousands?  I have a quick trick.

If you’re writing some T-SQL to check sums or maybe counts, it’s usually pretty important to write down the RIGHT amount, right?  I mean, if you’re going to miss the ‘2’ in the thousands column cause you just couldn’t see it (maybe not enough coffee?), it’s going to probably cost you a lot of time to redo that work and refind those values right?

Well, I think I have a quick trick.  The hiccup is I can never remember, so I’m writing this post so I have something to copy’n’paste from.

declare @reallyBigNumber int = 2282613
select CAST(CONVERT(varchar, CAST(@reallyBigNumber AS money), 1) AS varchar)

There’s TWO pieces of magic in the above sql.  First is the casting to money, then the conversion using the style of 1 with the CONVERT function.  The last cast I believe is meant to display it properly, I believe this could optionally be skipped.

If you don’t believe me, check out what happens when each small piece is broken out.

There is ONE caveat with this!!!!!!  Most senior devs will be chomping at the bit yelling at their screens like the Swedes today yelling into their screens for the 2014 Olympic men’s hockey final…..NNNNOOOOOO!!!!!!!!!  Remember, waaaaay up top, I said IF your writing sql to check stuff, or investigate or analyze!  I would REALLY suggest NOT doing this for production systems in any way!  If you’re looking for format your output, I think you’re doing it at the wrong place and SHOULD be looking at your client app instead of here on the db server.

Now that you know how to quickly differentiate your millions from your thousands, it’s time to grab a coffee and get coding!

 

Resources

MSDN: CAST and CONVERT (Transact-SQL)