convert from 0 to 1

[rsatyanag]

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 to convert 0 to 1. But it is converting even the number 10 to 11.

Can any one suggest me a solution?

thankyou
satyanag

[debasisdas]

You need to use DECODE instead of TRANSLATE.

[rsatyanag]

You need to use DECODE instead of TRANSLATE.

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

[debasisdas]

try like this sample code

[code=oracle]
select sum(decode((sys date-hiredate),0,1,( sysdate-hiredate))) from emp;
[/code]

[subashsavji]

[code=oracle]

select sum(DECODE((TO_ DATE(To_CHAR((h iredate),'yyyym mdd'),'yyyymmdd ') - TO_DATE(To_CHAR ((hiredate),'yy yymmdd'),'yyyym mdd')),0,1)) from emp
//

select sum(TRANSLATE(( TO_DATE(To_CHAR ((hiredate),'yy yymmdd'),'yyyym mdd') - TO_DATE(To_CHAR ((hiredate),'yy yymmdd'),'yyyym mdd')),0,1)) from emp
//

[/code]