Date Conversion

dodda · Post by **dodda** » Tue Oct 20, 2009 9:48 pm

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

chulett · Post by **chulett** » Tue Oct 20, 2009 10:02 pm

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?

ray.wurlod · Post by **ray.wurlod** » Tue Oct 20, 2009 10:26 pm

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

dodda · Post by **dodda** » Wed Oct 21, 2009 9:34 am

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

dodda · Post by **dodda** » Wed Oct 21, 2009 9:34 am

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

Sorry this is a server job

theone · Post by **theone** » Wed Oct 21, 2009 9:42 am

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

chulett · Post by **chulett** » Wed Oct 21, 2009 10:04 am

In DataStage use the following to convert your static date value to the same format as the date in your timestamp:

Code: Select all

OConv(IConv(YourField,"D"),"D-YMD[4,2,2]")

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.