Replace a particular date with null

[today]

Hi,

This should be a pretty stright forward question to all you gurus.

I have a field which is storing date in the format like(TIME_STAMP) :

2009-04-15 00:00:00
1900-01-01 00:00:00
2009-02-26 00:00:00

What will be the function to replace the dates "1900-01-01 00:00:00" to NULL.I wrote this function (which is working fine in oracle), but in datastage I am getting an error saying invalid number.


DECODE(posted_date,TO_DATE ('1900-01-01 00:00:00', 'yyyy-dd-mm hh24:mi:ss'),'',posted_date)

Can some tell me what should be the correct format?

Thanks in advance

[chulett]

You want to do this in DataStage or in Oracle? In the job, an If-Then-Else derivation would be perfectly fine:

Code: Select all

If Link.Field = "1900-01-01 00:00:00" Then @NULL Else Link.Field

[today]

Thanks for the quick reply.

I want to use this in a derivation column of dynamic DRS database.

[chulett]

Meaning, in Oracle in the target DML? Then any valid DML would work there. Why not do this "transformation" in the job itself?

[Sainath.Srinivasan]

Replace '' in your decode to NULL.