7453 : Interval Field Overflow issue

General
Enthusiast

7453 : Interval Field Overflow issue

Hello Friends,

 

I am trying to get age in years and months. I have written a case statement as below. Its showing the error interval field overflow. Data Type of both X and DOB are Date/time. Thanks in advance.

 

Case

When DOB = '1800-01-01' or DOB is Null then 'aaaa' ---------a represent a digit

else Cast(((Cast(X as Date)-Cast(DOB as Date)) Month(4)) as Integer)

end as Age in Months,

Case

When DOB = '1800-01-01- or DOB is null then 'aaa'----------a represent a digit

Else Cast(((Cast(X as Date)-Cast(DOB as Date)) Year(4)) as integer)

end as age in years

7 REPLIES
Enthusiast

Re: 7453 : Interval Field Overflow issue

Will this work for you?

 

SyntaxEditor Code Snippet

sel cast(date '2010-01-01' as timestamp(0)) currdate   ,cast(date '1963-01-01' as timestamp(0)) dob   ,case when cast(dob as date) = '1800-01-01' then 9999 else cast(((currdate - dob) month(4)) as integer) end as "age in months"   ,case when cast(dob as date) = '1800-01-01' then 999 else cast(((currdate - dob) year(3)) as integer) end as "age in years"   
Junior Supporter

Re: 7453 : Interval Field Overflow issue

Hi:

 

You'd better show the REAL CODE instead of things like "'aaaa' ---------a represent a digit".

 

Check the 'X' and 'DOB' data. It seems the substraction could be greater than 9999 months (about 830 years). Maybe there are some '0001-01-01' or '9999-12-31' or similar...

 

HTH.

 

Cheers.

 

Carlos.

Enthusiast

Re: 7453 : Interval Field Overflow issue

Hello Bhull63,

 

It didnt work for me.

Senior Apprentice

Re: 7453 : Interval Field Overflow issue

The easiest way to do an age calculation is based on MONTHS_BETWEEN:

Cast(Months_Between(Current_Date, dt) AS SMALLINT)      -- age in full months
Cast(Months_Between(Current_Date, dt) / 12 AS SMALLINT) -- age in full years
Enthusiast

Re: 7453 : Interval Field Overflow issue

Hmmm...that exact code ran fine for me. However, I think Dieter's suggestion is simpler, so I would go with that.

Enthusiast

Re: 7453 : Interval Field Overflow issue

Thank you Dnoeth.

It worked.

Junior Supporter

Re: 7453 : Interval Field Overflow issue

It's OK.

I do know how to calculate an age from two dates, but the question was about field overflow error.

Next time ask the question properly: "How do I calculate an age from two dates? "

Cheers.

Carlos.