string timestamp oracle

[devsonali]

Hello All
I have searched the forum and saw a lot of posts relating to string to time stamp conversion but not precisely what I need .

Oracle takes date field as timestamp .

In my job I need to load oracle table with a date field (metadata in DS shows as timestamp) from a string which is in yyyymmdd format.

Now string to timestamp function will work only if the format matches the timestamp , however , I can append a time and then use this function but i don t want to do it .

I wanted to check if there is a direct way to convert the above string into a timestamp field (actually a date) in oracle.

Thanks for looking

[pavi]

I think the explicit conversion has to take place.weather you do it in DS or in Oracle during insert.But check the default timestamp format in DS it is by default YYYY-mm-dd hh:mm:ss.its not an implicit conversion which can happen automatically.

[devsonali]

Thanks pavi

The problem with doing this in oracle stage is that I have to combine (funnal) data with another oracle table that has date as datatype prior to load it to oracle .

If I do not take care of this I cannot funnel data without warnings.

[chulett]

I can append a time and then use this function but i don t want to do it .

Why not? Append a zero time as part of the function call.

[devsonali]

Sure , I will if I do not find another way - I wanted to see (learn )if anybody knows (or there is a way in DS ) of a direct way to do it .

[chulett]

Out of curiousity, what do you consider to be a 'direct way'? To me, that is one. Do you perhaps mean an implicit conversion? If so, what is your source for this - a flat file, another database?

[devsonali]

Craig

I think by direct way I was trying to find out if there are any functions or logic that would handle this scenario .

When I add or concatenate the incoming string - It is for me (manipulation in terms of format i.e I am adding something to the string ) .

I hope I am clear. My incoming source is another oracle database (but the field itself is varchar and holds yyyymmdd . This has to funnel with another oracle source which has data as timestamp (its a date field but oracle metadata definition shows it as timestamp) . The target again is a timestamp.

Thank you

[chulett]

Well... you are transforming a date to a timestamp so I really don't see a way to do that without adding something for the time, specifically the zeroes you need. You can try using a TO_DATE() in your source SQL directly into a Timestamp field when selecting that field:

Code: Select all

TO_DATE(YOUR_FIELD, 'YYYYMMDD')

Otherwise, a TO_TIMESTAMP() with the zeroes specified but that's really no different than doing it in the job that you don't seem to like.

Code: Select all

TO_TIMESTAMP(YOUR_FIELD||'000000','YYYYMMDDHH24MISS')

[devsonali]

Craig

I think when I wrote

"Now string to timestamp function will work only if the format matches the timestamp , however , I can append a time and then use this function but i don t want to do it "

I kind of started on the wrong foot.

What I realize now and (after successfully testing)

There was no need what so ever to append anything
string to timestamp conversion with tagging the way input string will just work fine.
(which I think was the direct way i was referring )

Thanks again for all inputs from everybody

[chulett]

Good to know. I've just always preferred being... explicit.