Accomodating Date data in the format YYYYDDMM
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 126
- Joined: Thu Jul 01, 2010 11:39 pm
Accomodating Date data in the format YYYYDDMM
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!!!
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!!!
Mac4rfree
Re: Accomodating Date data in the format YYYYDDMM
Good Lord... why?mac4rfree85 wrote:Our project guideline is not to use OCONV & ICONV.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 126
- Joined: Thu Jul 01, 2010 11:39 pm
Re: Accomodating Date data in the format YYYYDDMM
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..
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..
Mac4rfree
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 126
- Joined: Thu Jul 01, 2010 11:39 pm
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 126
- Joined: Thu Jul 01, 2010 11:39 pm
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: