In our last post, we discussed fixing dates so that they’re more of what the users expect. In this post, I want to talk about changing numbers.
First off, it’s actually a very common thing to change a number from one type to another. For example, I’m often asked to change a decimal into an integer. Usually, these numbers end in “.0” or we just want to make them all into one format instead of having multiples.
One would think you’d be able to do something like the following:
SELECT CONVERT(INT, ‘1.0’)
SELECT CAST(‘1.0’ AS INT)
And you’d be wrong.
Both of these give big ole’ errors.
But, on the other hand, if you work with the following:
SELECT CONVERT(INT, CONVERT(decimal, ‘1.0’))
Then it’ll give you the value of 1 – which is exactly what you expect. Yes, this is now a two-step process but it works marvelously.
Step 1 – CONVERT from a string to a decimal
Step 2 – CONVERT from decimal to integer
Now you can try changing the number to anything with a decimal – for example, 24.7.
SQL Server will round the number up for you to 25.
If you don’t want it rounded, then you’ll have other items to accomplish – but, for now, I’ll leave that for you.
Also, remember that if there is even the slightest chance that the value you’re getting is not going to be a decimal – use the TRY_CONVERT function instead of CONVERT.
But the biggest item to remember when comparing CONVERT and CAST – pick the one you like and stick to it. You’ll notice that I use CONVERT almost all of the time. That’s because very early in my SQL career, I had to use the CONVERT function for a lot of date work – since CONVERT can easily change dates to different formats using 101, 102, etc… I would not expect too many others will enjoy seeing code that bounces back and forth between CONVERT and CAST.
And finally, be very careful when converting from an integer or decimal to a varchar. If the number is larger than the string allows, then you’ll end up with something your users will be confused by.
A great example of this is the following:
SELECT CONVERT(VARCHAR(4), 28675)
SELECT CONVERT(VARCHAR(4), 28.675)
One of these gives an error. The other does something you probably won’t want your users to see. Obviously, neither are good for you.
And with that, I’ll leave you to play, I mean, work, with SQL Server and CONVERTing or CASTing integers and decimals to different data types and back again. Until next time my friends!