CODEWITHSUNDEEP
sqldateoracle10gdatabase
Atul Kumar Verma
Atul Kumar Verma

Reputation: 369

Changing the data type "varchar'' of a column to "DATE" in SQL/ORACLE

I have a table in a database created in oracle 10G. It contains a column of type 'VARCHAR' and stores date as string in this format-> 'dd-mon-yyyy' eg: '12-aug-2008'. Now I want to change the datatype of this column from VARCHAR to DATE. but when i perfrom this query->

ALTER TABLE sales_order
MODIFY COLUMN delivery_date DATE;

I get following error

ORA-00905: missing keyword

I have also tried :

ALTER TABLE sales_order
ALTER COLUMN delivery_date DATE;

I got the error :

ORA-01735: invalid ALTER TABLE option

However when i try to add a fresh column with DATE datatype it works fine. example :

ALTER TABLE sales_order
ADD delivery DATE;

So, can anybody suggest me a way to change the datatype without deleting the column and its data.

Upvotes: 3

Views: 61112

Answers (6)

Shubhnish Verma
Shubhnish Verma

Reputation: 1

alter table employee add (DOB varchar(10));
if you add a column with datatype varchar and if you want to modify the datatype of DOB then you can use this command -> alter table employee modify(DOB date); Now the table is modified.

Upvotes: 0

Clayne Okallo
Clayne Okallo

Reputation: 21

Alternatively, you could create a new column, in which the data type is DATE. then pass the data in your varchar as a date .then drop your initial column and finally rename your new column to what it was initially...code below.

ALTER TABLE my_table ADD (new_col DATE);
UPDATE my_table SET new_col=TO_DATE(old_col,'MM/DD/YYYY');
ALTER TABLE my_table DROP (old_col);
ALTER TABLE my_table RENAME COLUMN new_col TO old_col;

Upvotes: 0

Siddhartha Chowdhury
Siddhartha Chowdhury

Reputation: 2732

Although its a pretty old question, I'll put my solution here for people seeking for a solution:

Here's my solution and it works perfectly.

ALTER TABLE `sales_order` CHANGE `delivery_date` `delivery_date` DATE;

Thank you

Upvotes: 2

Megui
Megui

Reputation: 1

Thanks for the hints! This got it for me.

alter table Table_Name Alter column Column_Name datatype GO

I too was needing to change from a VARCHAR to a date. I am working in SQL 2008 R2. I have found that if I bring in dates as a char or varchar and then change the type to date or datetime, I can catch time/date problems more easily.

THIS STATEMENT WILL FAIL IF YOUR DATA HAS ANY BAD DATES. (I break the date down into sections to find the bad date so I can correct and then I can alter the column type.)

Upvotes: 0

Vimal
Vimal

Reputation: 11

modify a column then syntax is:-

alter table table_name modify column_name datatype;

but when you modify the column datatype column must be empty

Upvotes: 0

paxdiablo
paxdiablo

Reputation: 882146

It's the first one, with a slight modification:

ALTER TABLE sales_order MODIFY (delivery_date DATE);

But I'm not sure that will work for those particular datatypes and it also may not work depending on the current data.

You may find it necessary in that case to:

  • create a new column X of date type.
  • populate X based on the old column (may need several passes of data fix-ups to work).
  • delete old column.
  • rename X to old column name.

Upvotes: 5

Related Questions