DATE To TIMESTAMP

marpadga18 · Post by **marpadga18** » Tue Feb 15, 2011 2:35 pm

Loading data from Oracle table to Oracle table
There is no source for this column
I created one MOD_DATE column in transformer
I need Date with timestamps in the target column
target table has MOD_DATE data type is DATE
I can load the date but not able to load date with time stamp

Any ideas I tried many ways could not find a solution any help appreciated?

jwiles · Post by **jwiles** » Tue Feb 15, 2011 5:22 pm

Create a timestamp column instead of a date column in your transformer. You can use '00:00:00' as the time portion of the timestamp when you create it, or whatever is considered valid by the customer.

Regards,

marpadga18 · Post by **marpadga18** » Tue Feb 15, 2011 5:44 pm

If i make date to timestamp then it is loading date.
I tried this DSJobStartDate:CurrentTimestamp() in the transformer for mod_date column it is loading date only when the datatype is changed to date in transformer but if i change to time stamp then also it is loading date in the target column
now my question is can we load time stamp into date datatype?
any ideas to over come the issue?

jwiles · Post by **jwiles** » Tue Feb 15, 2011 8:02 pm

Oracle's Date datatype is a timestamp internally, so you should load it with a timestamp column if you are wanting specific time information. However, the default view for that datatype only shows the date. Google for "Oracle date datatype"...Databasejournal.com has a good explanation of the datatype.

The method you used to build your timestamp was incorrect...you're concatenating a data and a timestamp. DSJobStartDate:CurrentTimestamp() would end up building a string looking like "2011-02-152011-02-15 19:52:12" (you're using the concatenation operator : so the date and timestamp are first converted to strings).

Build your timestamp correctly. CurrentTimestamp(), DSJobStartDate : ' 00:00:00', or whatever you need to store in the column. Leave the datatype as timestamp when it loads. Then view the column correctly in Oracle to see the time portion of the timestamp.

Regards,

marpadga18 · Post by **marpadga18** » Wed Feb 16, 2011 5:19 am

Yes you are right I used this query to check the output in oracle sql tool

SELECT study,mod_user,(TO_char( Mod_date, 'DD-MON-YY HH24:MI:SS' ))
FROM xxxx;
study mod_user date
Result = xxxx |yyyy |16-FEB-11 11:07:55

Thanks for your clarification