• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2618
  • Last Modified:

Do you know how I can avoid the SQL error: Arithmetic overflow error converting expression to data type nvarchar when using SQL Server 2005?

I am developing a SQL Select Statement using SQL Server 2005.

In the following statement, MthFrom is of type int and DollarTitle is of type nvarchar(50).
I tried casting the MthFrom of data type int to nvarchar but I got an error.

select * from dbo.tblSummModRev
order by DollarTitle + CAST(MthFrom As nvarchar(1)) Desc

When I execute the above SQL Select statement, I get the error:

Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type nvarchar.
I tried using Concatenation. Do you know how I can resolve this error?
0
zimmer9
Asked:
zimmer9
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
your code looks fine, should not result in that error.
anything else that could produce that error?
I mean, if you remove the order by, the problem is still there ...
0
 
zimmer9Author Commented:
The following works fine ...

select * from dbo.tblSummModRev
order by DollarTitle
0
 
Anthony PerkinsCommented:
As angelIII has alluded to the problem lies in DollarTitle + CAST(MthFrom As nvarchar(1))

You need to allow for October through November as in:
DollarTitle + CAST(MthFrom As nvarchar(2))

In other words allow one character for every digit in MthFrom.  If MthFrom can be say 8 digits than it needs to be:
DollarTitle + CAST(MthFrom As nvarchar(8))
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now