inserting timestamp into database

sdfasih · Post by **sdfasih** » Thu Jul 27, 2006 4:46 pm

Hi ,
I am trying to insert 0000-00-00.00.00.00.000000 into oracle database but's its giving me an error "invalid number".
Thanx

chulett · Post by **chulett** » Thu Jul 27, 2006 5:32 pm

How are you trying to insert it? What is the target Oracle datatype? Details, man, details!

lstsaur · Post by **lstsaur** » Thu Jul 27, 2006 6:22 pm

Hi,
Oracle does not have a timestamp datatype. Therefore, timestamp fields intended for an Oracle target will need to be converted to a [b]date[/b] datatype.

chulett · Post by **chulett** » Thu Jul 27, 2006 8:15 pm

lstsaur wrote:Oracle does not have a timestamp datatype.

Oracle has a TIMESTAMP datatype. Been there since 9i, I do believe.

lstsaur · Post by **lstsaur** » Thu Jul 27, 2006 9:59 pm

Hi Craig,
You are absolutely correct. It's time for me to throw away my Oracle 7 Server SQL Language Reference Manual and also to notify Ascential to update their DataStage EE training manual. Thanks.

ray.wurlod · Post by **ray.wurlod** » Fri Jul 28, 2006 4:53 am

It's certainly not a valid number - it has two "-" characters in it for a start. What is the data type of the target column? Are you using generated or user-defined SQL?

gbusson · Post by **gbusson** » Fri Jul 28, 2006 7:12 am

I'm not quite sure that 0000-00-00.00.00.00.000000 is a valid timstamp.

Have u tried 0001-01-01.00.00.00.000000?

sb_akarmarkar · Post by **sb_akarmarkar** » Fri Jul 28, 2006 7:12 am

sdfasih wrote:Hi ,
I am trying to insert 0000-00-00.00.00.00.000000 into oracle database but's its giving me an error "invalid number".
Thanx

Is it timestamp or number you are trying to insert

....

Thanks,
Anupam

kumar_s · Post by **kumar_s** » Sat Jul 29, 2006 12:15 am

Yet no answer for the first question asked.

loveojha2 · Post by **loveojha2** » Mon Jul 31, 2006 12:14 am

The error suggests that it is trying to take in a numeric data type column (specified within the datastage), change it to timestamp and see how it works.

kumar_s · Post by **kumar_s** » Mon Jul 31, 2006 4:34 am

loveojha2 wrote:The error suggests that it is trying to take in a numeric data type column (specified within the datastage), change it to timestamp and see how it works.

Sometimes it gives the same error if timestamp is not inserted in proper format.

kaps · Post by **kaps** » Mon Jul 31, 2006 8:48 am

Check the NLS date time format of your database and convert it accordingly. Normally it would be yyyy-mm-dd hh24:mi:ss.
No microseconds like DB2.

Thanks

DSXchange

inserting timestamp into database

inserting timestamp into database

Re: inserting timestamp into database