Date warning ,while loading into oracle table

[kollurianu]

Hi All,

Iam getting following warning when iam loading into the Oracle table


BUSINESSATTCOMLOADTBLE0605..fmtrec: At row 15886, link "updtrec", while processing column "ACTIVATE_DT"
Value treated as NULL
Attempt to convert String value "2005-10-19 11:21:38" to Date type unsuccessful

but all the records got updated,

here is the transformation i applied on the date field

if Not(IsNull(insandupdtrec.Activate_Date)) Then TrimB(TrimF(insandupdtrec.Activate_Date))[1,19] Else @NULL

and the input date format in the file is as follows,

2005-10-19 11:21:38.0

Could some one please shed some light on this...

Thanks to all ,

[kris007]

Are you using OCI stage?
Is the datatype for the column in question is date or Timestamp?
Are you using genereted query or user-defined query to insert rows?

Answers to these questions might lead to a correct answer IMHO.

Kris.

[DSguru2B]

What do you want your result to be ?
If you desire it to be : 2005-10-19 11:21:38
then just simply use a Field function to get the result.

[kris007]

then just simply use a Field function to get the result.

Eventually, I was gonna get there too.

[chulett]

The substring of the first 19 characters should be fine, there's nothing "wrong" with the timestamp value in the "Attempt to convert" message. Kris asked the key questions... and the answers should be 'Yes', 'Timestamp' and 'Generated' in that order.

[kollurianu]

it is an oci stage, thanks for all the responses,

so what is the difference between data and timestamp data types...


iam using a generated sql...

could some one clarify the difference between timestamp and date in oci stage.
thanks all of you,

[chulett]

If you read the OCI stage pdf documentation, the difference will become clear. In a nutshell, the stage will generate either a TO_CHAR() or a TO_DATE() function into your sql automagically and the format of the mask will change depending on the datatype and the action.

I always stick with Timestamp when dealing with Oracle DATE fields so the time portion is under my control:

Code: Select all

Timestamp:  TO_DATE(YourField,'YYYY-MM-DD HH24:MI:SS')

Change the datatype to a Timestamp in the target OCI stage and it sounds like you'll be fine. You'll see it is then expecting exactly the same format as you are supplying.

[hhh]

Hi,
Pl refer viewtopic.php?t=100893 link , this will through u some lights on your issue.

[sudhakar_viswa]

Hi kollurianu,

Your date format is 2005-10-19 11:21:38.0.

Observe once this format.It contains 0 as extra at the end.Thats why it could not able to convert.So,you have to apply the substrings command
also.

Thanks,
sudhakar

[sb_akarmarkar]

Is the generated query showing to_timestamp('2005-10-19 11:21:38.0','YYYY-MM-DD HH:MI:SS:FF') ? if not you can go head with user defined or else default format is to_timestamp('19-oct-05 11:21:38.0','DD-MON-YY HH:MI:SS:FF') so in transformer get date to the above default format and insert.

Date will not take fractional second precision as timestamp do...............

Thanks,
ANupam

[kollurianu]

Thanks for all your responses which helped..

yes , all the warnings are gone when i changed the datatype to

timestamp in OCI stage, however i have doubt , as i was loading

about 15000 rows and as almost all the date fields are populated,

all times job should abort , as it will exceed 50 warnings, i ran twice , once

it was aborting as the warnings limit exceed and other time , it loaded all
of records with some warnings..

this part was not clear, it should fail even on second run...

why was this happening..?

Any ideas..

Thanks all

[chulett]

If 'some warnings' on the second run was less than your limit (which defaults to 50 as you've seen in the Director) then no, it should not have 'failed'.

[kris007]

But Craig, why would the job finish with different number of warnings when run 'n' number of times. In that case, it might finish with few warnings once and it might abort when it hits the default number of warnings.

No matter how many times we run the job, if the datatype is set to DATE instead of TIMESTAMP it should abort everytime. Am I thinking in the correct direction?

[chulett]

Hard to answer without knowing the exact job design.

Does 'n number of times' mean each run was done with the same input data set, or with different data each time? Was the target reset between each run or allowed to accumlate changes?

Unless you duplicate the process exactly each time, more than likely you won't get exactly the same outcome each time. You would need to understand why the warnings you are seeing are being generated - or not being generated - from run to run.

[kris007]

Hard to answer without knowing the exact job design.

I agree. Since the number of rows( loaded into target) in question was 15000 rows from OP, I was taking into consideration that each job run was run with the same input dataset. But you are right that the process might not have been duplicated exactly from run to run( the input dataset might be different for each job run) and hence different number of warnings for each job run.