0

I have loaded the date field with dates and type is varchar.

How to convert date field(varchar) to date field(date) in oracle express/sql loader while displaying the fields?

RRR
  • 145
  • 1
  • 3
  • 11
  • I'm not sure that I understand what you are asking. Are you asking how to change the data type of a column in a permanent table? How to use SQL*Loader to load data into a `DATE` column in a table? How to change an external table definition from `VARCHAR2` to `DATE`? Or something else? – Justin Cave Jul 15 '13 at 21:10
  • I have already loaded with type varchar. Now for comparing with other databases , i need to convert the datatype to DATE in order to be compatible with the fields of other databases – RRR Jul 15 '13 at 21:14

1 Answers1

0

You can't change the data type of a column in a permanent table from VARCHAR2 to DATE when it has data.

You can, however, add a new column

ALTER TABLE table_name
  ADD( new_date_column DATE );

move the data over

UPDATE table_name
   SET new_date_column = to_date( old_varchar2_column, format_mask );

drop the old column

ALTER TABLE table_name
  DROP COLUMN old_varchar2_column;

and then rename the new column to the old column name

ALTER TABLE table_name
  RENAME COLUMN new_date_column TO old_column_name

Of course, once you do this, you'll need to change your SQL*Loader script to convert the data to a DATE if you ever want to load into this table again.

Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • I dont want to change any characteristics of columns, but just wanted to display as date. Whether that is possible? Using with any function like cast...? – RRR Jul 17 '13 at 16:43
  • @RRR - I'm still confused about exactly what you're asking. Perhaps you just want to call the `to_date` function on the column as part of your comparison (though that will be rather slow)? – Justin Cave Jul 17 '13 at 20:00