Thank you for your reply:
I have used DECODE but it is giving the number of records which are 0, but i have to apply the sum function over all.

select sum(decode((To_ Date((DATE2),'y yyymmdd') - To_date((DATE1) ,'yyyymmdd')),0 ,1))

Can you please give your suggesion on this

thankyou
satyanag...

Hi,
Iam generating a query in which the following fields are used:
date1, date2 of data type character. I am calculating the difference between two dates and applying sum function. query is as follows

select sum(translate(( To_Date((date2) ,'yyyymmdd') - To_date((date1) ,'yyyymmdd')),0 ,1)) from disdate

problem 1:- if dont use translate when date1 and date2 are equal it is giving value 0 so i used translate...