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

Posted on 2012-08-27
Last Modified: 2012-08-27
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?
Question by:zimmer9
    LVL 142

    Assisted Solution

    by:Guy Hengel [angelIII / a3]
    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 ...

    Author Comment

    The following works fine ...

    select * from dbo.tblSummModRev
    order by DollarTitle
    LVL 75

    Accepted Solution

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

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now