Solved

How to Safely Perform Data Type Conversion in an Oracle Table

Posted on 2011-03-17
1
257 Views
Last Modified: 2012-05-11
Dear experts,

We are using Oracle 11 Database and we have more than a dozen of "very large" tables. All the tables have a field (column) with Time Stamp (3) type. How can we convert these fields to DATE type  in a safe way without recreating table?

Note: The truncation of the Time Stamp field (seconds fraction etc.) is not important. Year, Month, Date, Hour, Minutes are enough.

BR,

0
Comment
Question by:GurcanK
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 4

Accepted Solution

by:
qasim_md earned 250 total points
ID: 35155706
1) you have to update your table with the UPDATE statement to eliminate the seconds fractions:
UPDATE TABLE1
SET date_column = convert(datetime,date_column)

2) After the table data is updated and the seconds fraction is removed ALTER the table column to the Datatype you want.

did that help ? let me know...
0

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

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…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to recover a database from a user managed backup
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

726 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