DSXchange

Hi Guys,

The source column is a Char field. The target is a date field. In the server job, the transformation was taken care by the datastage itself.

When the data is in the format of YYYYMMDD, the records are getting inserted. But if the record is in the YYYYDDMM format, it is throwing an error saying invalid month.

Our project guideline is not to use OCONV & ICONV. So without this, is it a way to handle YYYYDDMM column data.

Thanks for your help in advance & i hope i made myself clear..

Cheers!!!

mac4rfree85 wrote:Our project guideline is not to use OCONV & ICONV.

Good Lord... why?

Are your records always coming in YYYYDDMM format or is it mixed with the other format? For the former, you just need a different Oracle TO_DATE() mask. Or substring it back around the other way.

Sorry for the late reply.. its a long weekend here..

No idea Why we are not using OCONV and ICONV.. thats what we are doing..

As of now, the column comes only with YYYYDDMM format only. In the insert query, To_DATE() function is being used. Still, it is throwing an invalid month errror..

What date picture string did you specify with the TO_DATE() function? Was it "YYYYDDMM" or was it "YYYYMMDD"?

yeah Ray,, now i am getting your point...

on further analysis, i found that the data contains both YYYYMMDD and YYYYDDMM format..

Sorry to state the problem wrongly..

That's... unfortunate. You'll need to detect the format and standardize on one but - how will you know for certain which is which?

You have a business rule problem; ambiguous dates. For example, what is the date of 20110406 ? Is it April 6th or June 4th? You must resolve that before you can do anything sensible with the data.

Yeah i got your point.. sorry for the late response.. the Buissness ppl r working on this.. thanks for your help

Congratulations for finding the correct (and only) solution. It's a business problem that can only be resolved by the business.