Date format problem
Moderators: chulett, rschirm, roy
Date format problem
Hi,
my source is flat file and target is Oracle iam trying to convert my date format: 20060815 to 15-AUG-2006 by using Oconv and iconv functions as:
oconv(iconv(date, "DYMD"), "D-DMY[2,A3,4]")
but iam getting Error as: Attempt to convert String value " 15-AUG-2006 " to Timestamp type unsuccessful
anybody please answer me.
Thanks in advance
my source is flat file and target is Oracle iam trying to convert my date format: 20060815 to 15-AUG-2006 by using Oconv and iconv functions as:
oconv(iconv(date, "DYMD"), "D-DMY[2,A3,4]")
but iam getting Error as: Attempt to convert String value " 15-AUG-2006 " to Timestamp type unsuccessful
anybody please answer me.
Thanks in advance
What are your datatypes in source and target? In Btw, try this
Code: Select all
Oconv(Iconv(date[1,4]:'-' :date[5,2]:'-':date[7,2],"DYMD"),"D-DMY[2,A3,4]")
Kris
Where's the "Any" key?-Homer Simpson
Where's the "Any" key?-Homer Simpson
Re: Date format problem
That would be because the format you chose is not appropriate for a Timestamp datatype. You would need to convert it to YYYY-MM-DD format, not DD-MON-YYYY. Technically, the OCI stage is expecting this for a Timestamp:vsi wrote:but iam getting Error as: Attempt to convert String value " 15-AUG-2006 " to Timestamp type unsuccessful
Code: Select all
YYYY-MM-DD HH24:MI:SS
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 503
- Joined: Wed Jun 29, 2005 8:14 am
See if this ICONV works. DOnt have access to DataStage so cannot test it at the moment. Try it
Code: Select all
OCONV(ICONV(Date, "DYMD[4,2,2]": @VM : "MCN"),"D-DMY[2,A3,4]")
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Which typically means that the Oracle target type is a DATE field with the DataStage datatype declared as a Timestamp, the default when importing metadata via the OCI plug-in. With that combination and generated SQL, the stage wraps a TO_DATE around the field:
Now, if you define your DataStage job's datatype as a Date, the generated DML is different:
The OP would need to clarify what their situation is. The moral of the story is the fact that you need to be cognizant of how the OCI stages work for the various datatypes - especially DATE fields - and deliver the data in whatever format the stage is expecting. BTW, this is spelled out in the .pdf documentation for the stage.
[soapbox]
In my opinion, it's best to adopt a standard methodology that all developers use when dealing with Oracle dates, one that leverages the behaviour of the stage. Enforce that via a series of Oracle date handling routines. I don't believe any approach that treats dates as Varchars and relies on the NLS_DATE format of the database is a valid approach and is fraught with peril. One that takes advantage of the stage and standardizes on an ISO Standard Timestamp format - including a zero timestamp when the date "doesn't have" a time portion - is the only way to go. It will consistantly work regardless of the NLS_DATE format of the Oracle database and make your DATE field woes a thing of the past.
[/soapbox]
Code: Select all
TO_DATE(YourField,'YYYY-MM-DD HH24:MI:SS')
Code: Select all
TO_DATE(YourField,'DD-MON-YY')
[soapbox]
In my opinion, it's best to adopt a standard methodology that all developers use when dealing with Oracle dates, one that leverages the behaviour of the stage. Enforce that via a series of Oracle date handling routines. I don't believe any approach that treats dates as Varchars and relies on the NLS_DATE format of the database is a valid approach and is fraught with peril. One that takes advantage of the stage and standardizes on an ISO Standard Timestamp format - including a zero timestamp when the date "doesn't have" a time portion - is the only way to go. It will consistantly work regardless of the NLS_DATE format of the Oracle database and make your DATE field woes a thing of the past.
[/soapbox]
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers