Problem in loading a date

[Amit_111]

Hi All,

I am facing one strange problem. I have a DataStage job created in Windows Environment, in which I use a Date() function to pass Sys Date in the Target DataBase(Oracle9i). Its working fine.
But when I Executed the same Job in UNIX Environment it throws a Warning Message saying "A non-Numeric character was found where a numeric was expected". The Oracle Error code thrown is : ORA 01858
Please Help.

[chulett]

Not nearly enough information to be able to help. Except to say I'd wager it's because you are not explicitly handing your date transforms but making assumptions about the formatting.

You also need to verify that Date() returns the same information on both operating systems. I would think they would but you need to check.

[DSguru2B]

Date() inside the transformer or OS level date function? What expression are you using with Date()? Date() creates internal format. Now its upto you to change that to the format you require using the OCONV() function.

[Amit_111]

I am using the date() function from the Transformer Stage. The database format(scale etc.) is same for both the database. so if it works on windows environment it should work on Unix. Nothing has been changed from job level to the database level except the Environment.

Is it that Unix excepts something more specific than windows( windows might be automatically converting the date format from the date function to the Database format and Unix might not be able to do this).
Is it so?

[DSguru2B]

I have not worked on windows so I will let someone else give the insight on that. Unlike DB2, oracle does not like internal formats for dates. It has to be in YYYY-MM-DD format. I dont know how this would have worked in windows keeping in mind that the DSEngine is the same for both the platforms.

[nick.bond]

Write it out to a sequential file and show us what the value is,.

[Amit_111]

I will be able to show you this value only on Monday since my weekend has already started .
Till then please suggest if you get any idea about this issue.

[chulett]

Post your transforms on this field and the sql you are using in the target stage.

[rafik2k]

Also check the date format at the target database in both windows and unix environment.
As per target date format u need to customize the date() function accordingly.

[rafik2k]

Also check the date format at the target database in both windows and unix environment.
As per target date format u need to customize the date() function accordingly.

[yiminghu]

Try to use timestamp(date()) instead of date()