convert long varchar2 string to a timestamp

Guys

I have a field in oracle 10g called objversion it contains a long string which is the date/time for the row. its currently in varchar2 I want to view this in a report as a timestamp

ie
objversion
20050131104021

I want it as a timestamp in a new view

Any ideas

Regards
DarrenJacksonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Use to_timestamp with the correct format mask (I'm guessing at the format mask):
to_timestamp(column_name,'YYYYMMDDHH24MISS')


http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions213.htm#SQLRF06142
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark GeerlingsDatabase AdministratorCommented:
So, the simple answer is: create a view that selects this column with the "to_timestamp" operator that slightwv suggested, plus the other columns you need from this table.

Maybe this will work for you with no problems.  I say "maybe" because whether this works well for you, or not, depends on how clean this data is.  As long as *EVERY* record in this table has values in this column that convert automtically to valid timestamps using the same format mask, you'll be OK.  My guess is though that your data may not be 100% clean.  If that is true, and there are some invalid and/or incomplete values in this column, that view will return an Oracle error, and it won't even identify the problem record(s) to you.

Try a view like this though.  If you get errors, we can help you write a more-complex view that includes a function with an exception-handler to identify the problems record(s).
0
DarrenJacksonAuthor Commented:
Thanks for the update I'm quietly confident that this isn't the case and the data is good. But I take your notes with me.

Thank you.
0
DarrenJacksonAuthor Commented:
Thank you
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.