Clever Geek Handbook

Sql oracle update date

My table table1

has a column date_txt

that includes 2/16/2011 12:00:00 AM

- column date_txt

is VARCHAR2 (250 Char)

.

My table table1

also has a column date

that is date

.

I would like to "update" my field:

The end result should be:

table1:

 date

 2/16/2011

So from table1

date_txt

"date" is required and update it to column date

as date.

Any ideas? I'm a bloody beginner.

+3


source to share


3 answers


You can use Oracle to_date function to convert string to date:

update table1 set "date" = to_date(date_txt, 'MM/DD/YYYY HH:MI:ss AM')


See how the SQL script works.

+2


source


column date_txt which includes 2/16/2011 12:00:00 AM

  • First, DATE has no format... What you see is for display to easily interpret the date value.

  • Second, you should never store DATE as VARCHAR2 . This is a big problem and bad design.

Now that you have a bad design, it's a good idea to fix it right now.



Follow these steps:

  • Add a new column with data type DATE .
  • Update the new column with the date values ​​from the old column using TO_DATE .
  • Drop the old column.
  • Rename the new column to the old column.

I already answered how to do it here fooobar.com/questions/2216183 / ...

+1


source


Try the following:

update table1 set date=
    (select to_date(date_txt,'dd/mm/yyyy') from table1 where id=yourId) where id=yourId;

Use primary key columns for identifiers.

If this is what you need every time. I suggest you use Triggers

0


source






More articles:


All Articles