Accomodating Date data in the format YYYYDDMM

mac4rfree85 · Post by **mac4rfree85** » Fri Apr 01, 2011 5:13 am

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

chulett · Post by **chulett** » Fri Apr 01, 2011 6:45 am

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.

mac4rfree85 · Post by **mac4rfree85** » Tue Apr 05, 2011 1:30 am

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

ray.wurlod · Post by **ray.wurlod** » Tue Apr 05, 2011 2:26 am

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

mac4rfree85 · Post by **mac4rfree85** » Tue Apr 05, 2011 5:08 am

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

chulett · Post by **chulett** » Tue Apr 05, 2011 6:44 am

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

ray.wurlod · Post by **ray.wurlod** » Tue Apr 05, 2011 3:36 pm

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.

mac4rfree85 · Post by **mac4rfree85** » Mon Apr 11, 2011 6:38 am

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

ray.wurlod · Post by **ray.wurlod** » Mon Apr 11, 2011 4:30 pm

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

DSXchange

Accomodating Date data in the format YYYYDDMM

Accomodating Date data in the format YYYYDDMM

Re: Accomodating Date data in the format YYYYDDMM

Re: Accomodating Date data in the format YYYYDDMM