datetime formatting

[Former Member]

Hello forum! I REALLY need some help formatting a DATE field correctly.

I am working with the newest Data Services release, which I believe is 4.0. The server is Windows 2008 R2 and my client is running Windows 7 Pro. My source database is Oracle, and the data type is TIMESTAMP(6). The target DB is SAP Hana with a data type of TIMESTAMP.

The actual data in the column I am working with contains dates in the form "02-APR-10 02.39.26.863348000 AM" and "30-JUN-10 01.52.58.424077000 PM" - verified by viewing the data in Textpad. Perhaps I am making this more complicated than need be but I have yet to figure out what format "mask" to specify in File Format Editor to correctly handle this data. The "AM" and "PM" values are throwing me off.

I appreciate any help determining the correct way to format this data so DS inserts TIMESTAMP values into Hana. Thank you!!

~Ben

[Former Member · Accepted solution]

DS doesn't support AM , PM datetime format, so you will not be able to read the datetime as datetime

instead read it as varchar and use a custom function to convert this string date time to datetime format supported by DS like YYYY.MM.DD HH24:MI:SS etc

or if you know some utility which can do this, call that in custom function using exec and convert the string to datetime format

does oracle provides conversion function for this ?

[Former Member · Accepted solution]

Digging a little deeper into this, I discovered that the data I am working with was spooled to a text file using Oracle SQL Developer. Running the exact same query on the same DB, Schema, and Table resulted in the data being spooled as mm/dd/yyyy hh24:mi:ss.ff6 (04/15/2010 13:36:24.723272 for example).

So, it seems I can get around the trailing AM/PM for now, but am still having trouble getting this revised format into a TIMESTAMP field in Hana. I appreciate any "best practices" regarding how to format this field for insertion to Hana.

Thanks!

~Ben