string timestamp oracle
Moderators: chulett, rschirm, roy
string timestamp oracle
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
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
Re: string timestamp oracle
Why not? Append a zero time as part of the function call.devsonali wrote:I can append a time and then use this function but i don t want to do it .
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Re: string timestamp oracle
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 .
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
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
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:
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_DATE(YOUR_FIELD, 'YYYYMMDD')
Code: Select all
TO_TIMESTAMP(YOUR_FIELD||'000000','YYYYMMDDHH24MISS')
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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
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