timestamp insert to Date datatype to oracle

hargun · Post by **hargun** » Tue Oct 15, 2013 7:27 pm

hi,

i have design inserting from DB2 stage to oracle as target.

The column RATE_DATE(TIMESTAMP) in DB2 STAGE and target is oracle having RATE_DATE(DATE) as datatype.

i have tried by inserting the same timestamp to DATE datatype without any conversion and job work fine but i am not able to get the time only date is inserted.

Please help me on this.

Niv0599 · Post by **Niv0599** » Tue Oct 15, 2013 8:00 pm

Did you look at the target data RATE_DATE in oracle table to see if the Timestamp portion is also stored.

chulett · Post by **chulett** » Tue Oct 15, 2013 8:18 pm

Declare the RATE_DATE column with a data type of Timestamp in the job.

hargun · Post by **hargun** » Wed Oct 16, 2013 7:18 am

i am doing the same giving the timestamp datatype still storing the Date part only.

chulett · Post by **chulett** » Wed Oct 16, 2013 7:27 am

How exactly are you verifying this? What does the insert SQL in the stage look like, does it include a TO_DATE function for this field?

hargun · Post by **hargun** » Wed Oct 16, 2013 9:32 am

please find the SQL which the oracle stage generating

INSERT INTO DIM.STG_NAME(ASSIGNED_DATE) VALUES(:ASSIGNED_DATE).

IT doesn't include to_Date function for this field.