DSXchange

Hi All,

I want to perform the join operation on two columns of Oracle.

First column is having datatype Date and contain values in format : 7/10/2003 10:37:00 AM

Second column is having datatype Timestamp(6)
and contain value in format : 7/10/2003 12:00:00.000000 AM

when i am comparing the values from these two columns ,i am not getting any result in my outupt.

Please help me ,is there any standard function which will extract some part from these above two datatypes in specific format(i.e YYYY-MM-DD) to compare value between them or i need to build the custom transform.

Thanks In Advance!!

Several choices. You can TRUNC() in your SQL to get just the date portion. Or TO_CHAR() them both to strings in YYYY-MM-DD format and compare them directly. Or use IConv() and then compare the internal dates inside the job.

Hi Craig,

By using to_char() function,it is working fine.

But i didn't find any proper conversion code in Incov() function to convert these two values in same format. I want to use the Datastage provided function to perform this task,because i feel by using Oracle supported function and using them in queries slightly taking more time during execution.

Thanks!!

The ICONV for a date function will only use the date portion, so you should truncate the timestamp, i.e.

And for the record, using TO_CHAR() in your source query has no time/performance penalty to speak of.