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.
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,
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
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"
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...
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