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.
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.
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 ?
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