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
Date Conversion
Moderators: chulett, rschirm, roy
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?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Hi Chulett,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?
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
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.
Code: Select all
OConv(IConv(YourField,"D"),"D-YMD[4,2,2]")
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers