date comparison

[ranga1970]

SELECT TO_CHAR(AU_LEAD.POTENTIAL_DATE, 'YYYY-MM-DD HH24:MI:SS'), (AU_LEAD.ACS_CONTR_EFF_DATE, 'YYYY-MM-DD HH24:MI:SS'


are the two dates being retrieved from source through oraoc
I have not defined To_char, but Ora is automatically taking it, but my problem i need to only pass effective datec > 12-31-2002
'
I had mapped AU_LEAD.ACS_CONTR_EFF_DATE to DateProposedEffective at on transformer and its output lin is Xin_RFQ1
at the next transformer i am trying to put constraint

Iconv(' Xin_RFQ1.DateProposedEffective', 'D:MDY') > Iconv('12/31/2002', 'D:MDY')

but it is passing any records out of this transformerstage though about 200,000 records are coming to its in put and there are lot of records which fall after 12/31/2002

could some one help me where i went wrong in my comparison

[roy]

Hi,
In DS a date is the date part and time is the time part.
if you only need to check on the date part simply take the date part of the timestamp you got in your DB.
you could do it like this:

Code: Select all

Iconv(field(link.date," ",1),"D-MDY[2,2,4]") > Iconv("12-31-2002","D-MDY[2,2,4]")

IHTH,

[ranga1970]

It doesnot work

[ArndW]

Ranga,

if you did a cut-and-paste to your quoted code in this post then I can see the error - you have quotes around your derivation variable name so DataStage doesn't use the value but compares to the string, getting the same result for each row.

[ranga1970]

YES i MADE THAT OBSERVATION AND LATER CORRECTED IT BUR IT STILL DOESN'T WORK