Error when trying to replace one sub-query

[MuadDibII]

Hi all,

After several jours of testing and research, i decided to write on this forum that help me a lot of times.

I'm doing a realy big query in an Access aplication that i'm working now (it's an Access SQL Direct query to an AS400 data base):

At my WHERE clause, I've some sub-querys:

WHERE (ntlrp in(select noclie from pack10pdta.agposp0s where codde1='EUR' and etacpt<>3 and nacmpt in ('205','210','211','212','213','214','215','216','260','261','262','263','264','265','266','267','268')) 
or ntlrp in (SELECT  noclco FROM pack10pdta.fccotp0s where nocmpt in (select nocmpt from pack10pdta.fcgcop0s where etacpt<>'3' and nacmpt in ('213','214','216','262','264')))
or ntlrp in (SELECT  noclma FROM pack10pdta.fcmanp0s where nocmpt in (select nocmpt from pack10pdta.fcgcop0s where etacpt<>'3' and nacmpt in ('205','210','211','212','213','214','215','216','260','261','262','263','264','265','266','267','268')))
or ntlrp in (SELECT notdes FROM pack10pdta.ptit)
or ntlrp in (SELECT garant FROM pack10pdta.EPSGDEP000 where rfgara in (select rfgara  from pack10pdta.epsgdcp000  where  dtfin>'20100505')) 
or ntlrp in (SELECT garant FROM pack10pdta.EPSGDGP000 where rfgara in(select rfgara from pack10pdta.epsgdcp000 where dtfin>'20100505')))

And there's no problem there, it works fine.
My problem happens when I try to change one of this sub-querys (at line 5 here):

select rfgara  from pack10pdta.epsgdcp000  where  dtfin>'20100505'

for

select rfgara  from pack10pdta.epsgdcp000  where  cast(substr(replace(dtfin, '99999999', '99991231'), 1, 4)||'-'||substr(replace(dtfin, '99999999', '99991231'), 5, 2)||'-'||substr(replace(dtfin, '99999999', '99991231'), 7, 2) as date)>current date

There i've got the error:
[DB2 UDB]SQL0181 - A value of the date, time or timestamp is incorrect

I think it's important to tell that this small sub-query works very well alone, without my main query.

Does anyone have any idea of what's going on here?
Thanks for all answers.

[Amarnath Reddy N]

Date format you used in the query "(dtfin, '99999999', '99991231')" is not Proper,
I am not understanding the Date value '99999999', come under which Date format.

Follow the proper standards for Date Format, follow the below link which have guidelines Date format:

http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/index.jsp

[MuadDibII]

Thanks for your answer Amarnath,

The thing is that in my Database, this champs is not in DateTime format but in Text format, and the '99999999' means forever.

I fix this probleme with a CASE expression, instead of a REPLACE.