convert long varchar2 string to a timestamp

Posted on 2014-08-04
Last Modified: 2014-08-04

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


I want it as a timestamp in a new view

Any ideas

Question by:DarrenJackson
    LVL 76

    Accepted Solution

    Use to_timestamp with the correct format mask (I'm guessing at the format mask):
    LVL 34

    Assisted Solution

    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).

    Author Comment

    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.

    Author Closing Comment

    Thank you

    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Join & Write a Comment

    Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
    Introduction A previously published article on Experts Exchange ("Joins in Oracle", makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

    728 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

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now