DSXchange

Hi

I have a scenario where i have to convert a string ( which is constant and in the format 31-DEC-1981) to Date and compare that value with a Field which has been defined as Timestamp data type on the source side.

My design looks like

Oracle---Transformer----Oracle

Timestamp field from the source is in the format YYYY-MM-DD HH:MI:SS
and i have to compare this field with String (31-DEC-1981) by converting to date.

Please help me

What have you tried? IConv/OConv can handle getting your Date into the same format as your Timestamp but how will you handle the 'compare'? Meaning, will you need to convert your constant date to a zero time timestamp or do you need to compare it to just the date portion of the timestamp?

Server job (as marked) or parallel job (as posted) or sequence?

chulett wrote:What have you tried? IConv/OConv can handle getting your Date into the same format as your Timestamp but how will you handle the 'compare'? Meaning, will you need to convert your constant date to a zero time timestamp or do you need to compare it to just the date portion of the timestamp?

Hi Chulett,

I just need to compare only the date part of the timestamp with the format i have 31-DEC-1981 (which is string). I tried to use Iconv and Oconv but wasnt successful. While comparing i need get the both formats to be in sync. Wondering how to convert them to same format and do the comapre

Thanks

ray.wurlod wrote:Server job (as marked) or parallel job (as posted) or sequence?

Sorry this is a server job

use to_date('dd-mm-yyyy') in user defined sql

In DataStage use the following to convert your static date value to the same format as the date in your timestamp:
Then compare that to the first ten characters of your timestamp using YourTimestamp[1,10].

If this static string is coming in as a job parameter, do the O/Iconv in the initial value of a stage variable and then leave the Derivation empty so it is only evaluated once rather than once for every record.