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
Replace a particular date with null
Moderators: chulett, rschirm, roy
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
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom