Solved

TSQL CASE Statement Error SQLServer 7(Msg 8115, "Arithmetic overflow error converting numeric to data type numeric.")

Posted on 2003-11-28
2
1,296 Views
Last Modified: 2011-09-20
SQLServer 7
Watch out for this one…

Statement Below Works
Select
CASE d.AmPmAll
WHEN 1 THEN 0.5
WHEN 2 THEN 0.5
WHEN 3 THEN 1.0 --CORRECT
END TimeOfDayNum
from  Dayoff d


Statement Below Fails
Select
CASE d.AmPmAll
WHEN 1 THEN 0.5
WHEN 2 THEN 0.5
WHEN 3 THEN 1 --INCORRECT
END TimeOfDayNum
from  Dayoff d

Server: Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting numeric to data type numeric.

The ".0" makes a big difference eh!

FYI - d.AmPmAll is a tinyint field; Posted to save someone else time.
0
Comment
Question by:GJPerkins
  • 2
2 Comments
 
LVL 6

Accepted Solution

by:
lausz earned 20 total points
ID: 9837244
The problem is that you have to manage the same data type for all the case statement ..is for this taht you need to use 1.0-

0
 
LVL 6

Expert Comment

by:lausz
ID: 9837263
I tested your sample in sql 2000 and it works in the two ways. I don't know in 7.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

758 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

12 Experts available now in Live!

Get 1:1 Help Now