0

Apologies for asking a question that I suppose is very basic. I'm just starting out with Streams. I'm trying to read a sequential files containing a timestamp field and a value field, like this:

150601172623+0530 150601172041+0530

I want add this value to oracle 11g database as a timestamps. Is there any way in datastage 9.1.2 for this conversation...?

Result should be like below

150601172623+0530 -> 2015-06-01 05:26:23 PM 150601172041+0530 -> 2015-06-01 05:20:41 PM

Improve this question

1 Answer 1

Reset to default
0

You can use TO_TIMESTAMP_TZ Function:

SELECT 
   TO_TIMESTAMP_TZ('150601172623+0530', 'RRMMDDHH24MISS+TZHTZM'), 
   TO_TIMESTAMP_TZ('150601172041+0530', 'RRMMDDHH24MISS+TZHTZM') 
FROM dual;


2015-06-01 17:26:23.000000000 +05:30   2015-06-01 17:20:41.000000000 +05:30
Improve this answer
5
  • Thanx for this answer , Its correct , But I want output as a 2015-06-01 05:26:23 PM and 2015-06-01 05:20:41 PM for add to timestamp field in my database
    – Lilantha Lakmal
    Commented Jun 4, 2015 at 8:52
  • You get a TIMESTAMP. If you want to convert it to a string surround it by TO_CHAR(..., 'YYYY-MM-DD HH:MI:SS PM'). But you should not store a timestamp as sting in a database:
    – Wernfried Domscheit
    Commented Jun 4, 2015 at 10:14
  • Thanx you so much
    – Lilantha Lakmal
    Commented Jun 4, 2015 at 11:48
  • Is this a datastage function or oracle function. . ?
    – Lilantha Lakmal
    Commented Jun 7, 2015 at 5:27
  • An Oracle function
    – Wernfried Domscheit
    Commented Jun 7, 2015 at 6:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.