Solved

How to Cast VARCHAR2 to TIMESTAMP(6) In Select Statement In Oracle?

Posted on 2014-11-23
1
465 Views
Last Modified: 2014-11-23
Dear Experts,

I have a table with a field varchar2(24) with values like: '2012-09-27 01:40:31.0' (I think trailing spaces exist as well)

I want to perform insert into table select that field but the corresponding field in the target table is type TIMESTAMP(6)

How can I achieve this?

BR
0
Comment
Question by:GurcanK
1 Comment
 
LVL 34

Accepted Solution

by:
johnsone earned 500 total points
ID: 40460567
You just need to include a to_timestamp function on the character field.

insert into mytab (col1) select to_timestamp(col2, 'yyyy-mm-dd hh24:mi:ss.ff') from mytab;

If for any reason a value is not in that specific format, you will get an error.
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
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.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

813 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

11 Experts available now in Live!

Get 1:1 Help Now