SQLTeam.com | Weblogs | Forums

Numeric value overflow error

Transact-SQL
#1

Hi,

I have column called "label" having datatype numeric(8,2).

I am trying to run an update statment as follows on that column as follows

label=(datepart(day,rndtime-startime)*24+
Datepart(hour,rndtime-startime)*60+
Datepart(minute,rndtime-startime)/60)

I am getting an error numericoverflow while running the update statment

when I update the datatype of the column "label" to numeric(9,2) the update works without any errors.

Since I would not like to change the datatype if the column can anybody suggest how to identify the values in the label column which is execeeding and is the main reason for the error?

#2

There must be a thousands of days between the two dates. My best guess is that somehow startime got set to '1900-01-01', since that's a common date in SQL Server.

In order to have a consistent calc, you should not multiply the hours by 24, and you can drop the minute calc completely, since it will always be 0 anyway.

#3

Thanks for the reply scott, I am not coming across any issues when I change the datatype for the column "label" from numeric(8,2) to numeric (9,2).

Is there a condition that I can add to the query to identify the values that gives an error for numeric overflow while updating the column having datatype numeric(8,2) ?

#4

Check the "startime" value. I ran a check and noticed that if it is 1900-01-01 it will overflow (8, 2) (as I mentioned above).

#5

Can you please explain what this label column is supposed to represent, as i think there is probably a better way to accomplish…. With that being said you should be able to run

Select top 10 *,datepart(day,rndtime-startime)*24+
Datepart(hour,rndtime-startime)*60+
Datepart(minute,rndtime-startime)/60)

from mytable a
Order by (datepart(day,rndtime-startime)*24+
Datepart(hour,rndtime-startime)*60+
Datepart(minute,rndtime-startime)/60) desc

The rows causing the overflow will be first