Comparing Two Data Values

Vishal1982 · Post by **Vishal1982** » Mon Aug 10, 2009 6:36 am

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!!

chulett · Post by **chulett** » Mon Aug 10, 2009 6:39 am

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.

Vishal1982 · Post by **Vishal1982** » Mon Aug 10, 2009 9:58 am

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!!

ArndW · Post by **ArndW** » Mon Aug 10, 2009 10:32 am

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

Code: Select all

ICONV(FIELD(In.TimestampColumn,' ',1),"D4/DMY[2,2,4]")

chulett · Post by **chulett** » Mon Aug 10, 2009 10:37 am

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