trying to get decimal precision on date difference

Database

trying to get decimal precision on date difference

SyntaxEditor Code Snippet

CAST(
         CAST(date1 AS DECIMAL(4,2)) - CAST(date2 AS DECIMAL(4,2)) /365.00 AS DECIMAL(4,2))  AS AGE_AT_CLM_LN_DT

I'm trying to get decimal precision of 2 for 2 dates, but whatever I try is giving me no decimal precision. Been staring at this for a while so it's probably something stupid I'm missing, but would appreciate the help.  

3 REPLIES
Teradata Employee

Re: trying to get decimal precision on date difference

What are date1 and date2?  If they are defined as DATEs, then they have 7 integer digits, so if you don't cast them as at least DEC(9,2) then you will get a numeric overflow error.  Break it down into pieces: try simply 'select CAST(date1 AS DECIMAL(4,2)), CAST(date2 AS DECIMAL(4,2))' and see what you get.

Teradata Employee

Re: trying to get decimal precision on date difference

This says divide the numeric value of date2 by 365.00 and subract that from the numeric value of date1. I doubt that's the calculation you intend.

Maybe you want to subtract before you divide? (date1-date2)/365.00 ?

Teradata Employee

Re: trying to get decimal precision on date difference

I'll go with my own guess and suggest you this code :

select months_between(date1, date2) / 12.00 AS AGE_AT_CLM_LN_DT