[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

convert long varchar2 string to a timestamp

Posted on 2014-08-04
4
Medium Priority
?
620 Views
Last Modified: 2014-08-04
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
0
Comment
Question by:DarrenJackson
  • 2
4 Comments
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1600 total points
ID: 40238924
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
 
LVL 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 400 total points
ID: 40239183
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
 

Author Comment

by:DarrenJackson
ID: 40239197
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
 

Author Closing Comment

by:DarrenJackson
ID: 40239198
Thank you
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Suggested Courses
Course of the Month20 days, 12 hours left to enroll

865 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question