Hello there,
I have a fixed width file coming with one of the field( Char(8) ) having a date format
eg:20111122 and it has some empty values too.I have to load it to Oracle table as Timestamp which is NULLABLE and when i imported the table definitions to datastage it is imported as Timestamp with scale=6 and no length defined
I am doing a TrimLeadingTrailing of that column and used the below code
if Column='' then SetNull() else StringToTimestamp(Column:'00:00:00',"%yyyy%mm%dd %hh:%nn:%ss")
But, this is throwing me an error
Warning:::1.APT_CombinedOperatorController,0: Conversion error calling conversion routine timestamp_from_string data may have been lost
Fatal:::2.oracle_enterprise_stage,0: Failure during execution of operator logic.
Fatal:::3.oracle_enterprise_stage,0: Fatal Error: Invalid time
I tried replacing the oracle enterprise stage with a sequential stage for output, then the job ran successfully with a warning
APT_CombinedOperatorController,0: Conversion error calling conversion routine timestamp_from_ustring data may have been lost
Conversion of string to Timestamp
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
So, instead of
if Column='' then SetNull() else StringToTimestamp(Column:'00:00:00',"%yyyy%mm%dd %hh:%nn:%ss")
I need to specify
if Column='' then SetNull() else StringToTimestamp(Column:' ': '00:00:00',"%yyyy%mm%dd %hh:%nn:%ss")
Tried the above but the job aborted with Fatal errors below
Oracle_enterprise_stage,0:Failure during execution of operator logic.
Oracle_enterprise_stage,0:Fatal Error: Invalid time
main_program: Unexpected exit status 1
if Column='' then SetNull() else StringToTimestamp(Column:'00:00:00',"%yyyy%mm%dd %hh:%nn:%ss")
I need to specify
if Column='' then SetNull() else StringToTimestamp(Column:' ': '00:00:00',"%yyyy%mm%dd %hh:%nn:%ss")
Tried the above but the job aborted with Fatal errors below
Oracle_enterprise_stage,0:Failure during execution of operator logic.
Oracle_enterprise_stage,0:Fatal Error: Invalid time
main_program: Unexpected exit status 1
-
- Participant
- Posts: 15
- Joined: Thu Aug 07, 2008 2:56 am