CODEWITHSUNDEEP
sqloracle-databasedate
Ryan_W4588
Ryan_W4588

Reputation: 668

How can I easily change an oracle db "varchar" column to "date" when current format is mm/dd/yy

I have a column in an Oracle 11g Database which consists of dates, currently in a string. However, I want to create a query which fetches and orders based on the dates. I do not believe I can do this unless they are dates, not varchars. How can I change this column (of 7k rows) easily and efficiently?

Example:

alter table TABLE alter column COLUMN date not null

However, I'm worried this will make me lose my data. If it does, I can always back it up, but I just want to see if there a way around that tedious procedure.

Upvotes: 1

Views: 2627

Answers (2)

Jorge Campos
Jorge Campos

Reputation: 23381

And you can't change the column the way you want. You will have to create a temp date column

ALTER TABLE yourTable add tempDate date not null;

Then update it with the date formatted:

UPDATE yourTable SET tempDate = to_date( yourVarcharDateColumn, 'mm/dd/yy' );

Then you drop your column and recreate it

ALTER TABLE yourTable DROP COLUMN yourVarcharDateColumn;
ALTER TABLE yourTable ADD yourVarcharDateColumn DATE NOT NULL;

Then you Update it with the values in temp

UPDATE yourTable SET yourVarcharDateColumn = tempDate;

Then drop the temp column

ALTER TABLE yourTable DROP COLUMN tempDate;

Edit

As pointed by @beherenow in comments there is the option of RENAME a column that I forgot about.

So it would be:

  • Add the tempColumn
  • Update the tempColumn with the formated date
  • Drop the old column
  • Then rename the tempColum with the command:

ALTER TABLE yourTable RENAME COLUMN tempDate to yourVarcharDateColumn;

Upvotes: 5

Joshua Huber
Joshua Huber

Reputation: 3533

Pretty easy:

Example table that stores date values as VARCHAR2:

CREATE TABLE mytable (dt VARCHAR2(100));

INSERT INTO mytable VALUES ('7/15/2014');
INSERT INTO mytable VALUES ('7/16/2014');
INSERT INTO mytable VALUES ('7/17/2014');
INSERT INTO mytable VALUES ('7/18/2014');
INSERT INTO mytable VALUES ('7/19/2014');

Failed attempt with ALTER:

ALTER TABLE mytable MODIFY dt DATE NOT NULL;

ERROR at line 1:
ORA-01439: column to be modified must be empty to change datatype

Knew that would happen. Can't modify the column unless it's empty. No problem. Let's copy the table to a temp table via CTAS, then empty it so we can ALTER, and reload the data.

CREATE TABLE mytable_tmp AS SELECT * FROM mytable;

Table created.

TRUNCATE TABLE mytable;

Table truncated.

ALTER TABLE mytable MODIFY dt DATE NOT NULL;

Table altered.

ALTER SESSION SET nls_date_format='mm/dd/yyyy';

Session altered.

INSERT INTO mytable (SELECT * FROM mytable_tmp);

5 rows created.

DROP TABLE mytable_tmp PURGE;

Table dropped.

The result: now you have a proper DATE column that you can ORDER BY.

DESC mytable

Name   Null?    Type
------ -------- ----
DT     NOT NULL DATE

SELECT * FROM mytable ORDER BY dt DESC;

DT
----------
07/19/2014
07/18/2014
07/17/2014
07/16/2014
07/15/2014

Upvotes: 0

Related Questions