TSQL int/int overflow

Question

I have a column called Odo that contains the number of meters in a trip. I would normally divide that by 1000 to display the number of Km's.

The line of code in question:

convert(varchar(10), startPos.Distance / 1000)

causes the following error

Msg 8115, Level 16, State 8, Procedure sp_report_select_trip_start_and_stop, Line 7 [Batch Start Line 2]
Arithmetic overflow error converting numeric to data type numeric.

Msg 232, Level 16, State 2, Procedure sp_report_select_trip_start_and_stop, Line 9 [Batch Start Line 2]
Arithmetic overflow error for type varchar, value = 931.785156.

That number is clearly longer than my varchar. How do I divide, truncate to 1 decimal place and then convert?

Edit: SQL Server 2008 R2, so format() is not available

Side note: you should not use the sp_ prefix for your stored procedures. Microsoft has reserved that prefix for its own use (see Naming Stored Procedures), and you do run the risk of a name clash sometime in the future. It's also bad for your stored procedure performance. It's best to just simply avoid sp_ and use something else as a prefix - or no prefix at all! — marc_s
– marc_s, Commented Jan 24, 2019 at 4:58

Paul Maxwell · Accepted Answer · 2019-01-24 03:20:04Z

1

You can use format

format(startPos.Distance/1000,  '#,###,###.#')

Or,

convert(varchar(10),cast(startPos.Distance/1000 as decimal(9,1)))

You may wish to introduce round() into these as well. e.g.

format(round(startPos.Distance/1000,1),  '#,###,###.#')

or

convert(varchar(10),cast(round(startPos.Distance/1000,1) as decimal(9,1)))

answered Jan 24, 2019 at 3:20

Paul Maxwell

35.7k4 gold badges39 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Hecatonchires Over a year ago

2008r2 so no format() function available same result, using cast or convert.

Paul Maxwell Over a year ago

"Same result" ... to what?

Hecatonchires Over a year ago

I apologise. I tested again in a clean window and the last option (with round()) is perfect.

Collectives™ on Stack Overflow

TSQL int/int overflow

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related