arithmetic overflow error converting expression to data type datetime

Question

arithmetic overflow error converting expression to data type datetime

pragyan.banerjee

SSC-Addicted

Points: 438
More actions
March 23, 2012 at 11:01 am

#259066

Hi
This is my function
CREATE FUNCTION [dbo].[dateadd1] (@birth_date datetime)
RETURNS int
AS
BEGIN
--declare @datetime1 datetime
declare @datetime2 int
declare @datetime3 int
declare @value int
set @datetime2=(select max(time_id) from table)
set @datetime3=dbo.convert_date_to_time_id(dateadd(month, -1, dbo.convert_timeid_to_date(@datetime2)))
set @birth_date=dateadd(year,70,@birth_date)
if @birth_date > @datetime3
begin
set @value=1
end
else
set @value=0
return @value
END
I am getting this error when i am running this function,I am giving the parameter as isnull(birth_dt,getdate())
not sure still why i am getting this error?
Please help

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Not enough information. First, what it the value of birth_dt being provided in the isnull(), is it null? Also, you have a call to another function in this code. It would help to see it as well.

And, one more important thing, how about providing the full error you are receiving.

pragyan.banerjee SSC-Addicted Points: 438 More actions · Answer 2

Hi,

It may be null,plese check some sample values below for the parameter

1987-06-30 00:00:00.000

Null

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

pragyan.banerjee (3/23/2012)
Hi,
It may be null,plese check some sample values below for the parameter
1987-06-30 00:00:00.000
Null

And? What about the other things I asked?

pragyan.banerjee SSC-Addicted Points: 438 More actions · Answer 4

Hi,

This is the exact error I am getting

Server: Msg 8115, Level 16, State 2, Procedure dateadd1, Line 13

Arithmetic overflow error converting expression to data type datetime.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

pragyan.banerjee (3/23/2012)
Hi,
This is the exact error I am getting
Server: Msg 8115, Level 16, State 2, Procedure dateadd1, Line 13
Arithmetic overflow error converting expression to data type datetime.

And still don't have the code for the function called inside dateadd1.

Give us everything we need to help you. We can't see what you see and we aren't mind readers.

Ask yourself, if I knew nothing about the problem, could I help solve it with what I have provided.

Lowell SSC Guru Points: 323518 More actions · Answer 6

pragyan.banerjee (3/23/2012)
Hi,
This is the exact error I am getting
Server: Msg 8115, Level 16, State 2, Procedure dateadd1, Line 13
Arithmetic overflow error converting expression to data type datetime.

if that inner function you are calling is adding seconds, and daterange difference greater than 70 something years will cause an overflow

editok 68 years 20 days actually.

select power(2,31)

Msg 232, Level 16, State 3, Line 1

Arithmetic overflow error for type int, value = 2147483648.000000.

select dateadd(ss,2147483647,0) -- one less than the overflow for an integer

--Results: 1968-01-20 03:14:07.000

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!