findinf date difference of two dates in months

Database
Enthusiast

findinf date difference of two dates in months

Hi All,

I have two date columns , i have to find the date difference between sysdate and call_date the difference should be in months. i have to find the difference if it is more than 4 months i have to use the difference in where clause.

i have tried like

sel cast((date-call_dt as interval month(2)) as promt from table name
where prompt > 4

is there any function in teradata sql assistant by which this can be done.

any hekp will be highly appreciated.

3 REPLIES
Enthusiast

Re: findinf date difference of two dates in months

Something like that ?

select (extract(year from current_date) - extract(year from CALL_DATE)) * 12
+ (extract(month from current_date) - extract(month from CALL_DATE))
+ (case when extract(day from CALL_DATE) < extract(day from current_date)
then 0 else -1 end) as MONTHS
from MY_TABLE
where MONTHS > 4

Enthusiast

Re: findinf date difference of two dates in months

Thanks a lot...it's working
Teradata Employee

Re: findinf date difference of two dates in months

I know this is a bit late for you but it's a bit more compact and faster and may serve to somebody else:

SELECT ((CURRENT_DATE - date-call_dt ) YEAR(4) TO MONTH) (INTERVAL MONTH(4)) promt
FROM table name WHERE promt > 4