Search code examples
t-sqlsql-server-2008-r2type-conversionnumber-formatting

TSQL int/int overflow

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

Solution

  • 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)))