Clever Geek Handbook

How to extract the DATE year and insert it into a new column?

I have a column in my table that contains DATE and I only need to store YEAR (=> in type NUMBER or INT)

My idea was to add a column and try something like this:

UPDATE myTable SET newColumn=(
select to_number(to_char(oldColumn.year, 'yyyy')) from myTable)

But I am getting this error:

single line subquery returns more than one row

Could you help me?!?

+3


source to share


5 answers


You need to change your query to:

UPDATE myTable 
SET newColumn=to_number(to_char(oldColumn.year, 'yyyy'))


This statement SELECT

will return all the rows in the table, from the column oldColumn.year

that your "subscript" one-line query generates, returns more than one row ".

(select to_number(to_char(oldColumn.year, 'yyyy')) from myTable)

+3


source


You can use the function EXTRACT

:



UPDATE myTable 
SET    newColumn = EXTRACT( YEAR FROM oldColumn.year )
+2


source 


insert into table_name (v_date)
values ((select extract (year from current_date())));
+2


source


It is a bad idea. Storing only part of the date is unlikely to be useless. Also, you won't be able to do date arithmetic on a column.

To get the year value, Oracle provides a date and time format to display. For any year based date calculation you just need to do:

EXTRACT(YEAR FROM date_column)

If you still want to redesign and keep only a part of the date, use VIRTUAL COLUMN instead of creating a static column .

For example,

dt_yyyy NUMBER GENERATED ALWAYS AS (EXTRACT(YEAR FROM date_column)) VIRTUAL

Or

dt_yyyy NUMBER GENERATED ALWAYS AS (to_number(to_char(date_column, 'YYYY'))) VIRTUAL

NOTE . Virtual columns were introduced from 11g.

+2


source


This looks like an ideal situation where a virtual column can be used if you are using at least 11g. Consider this create table statement for my test table where v_year is a virtual column:

CREATE TABLE X_TEST
(
  COL2       LONG,
  COL3       VARCHAR2(50 BYTE),
  COL4       VARCHAR2(50 BYTE),
  COL5       VARCHAR2(50 BYTE),
  COL6_DATE  DATE,
  V_YEAR     NUMBER Generated Always as (EXTRACT(YEAR FROM "COL6_DATE"))
);

Now just insert the date and Bam! year in it automatically! No special update is required.

SQL> insert into X_TEST (col6_date) values (sysdate);

1 row created.

SQL> select col6_date, v_year from X_TEST;

COL6_DATE     V_YEAR
--------- ----------
22-MAY-15       2015

SQL>

More information and examples: http://oracle-base.com/articles/11g/virtual-columns-11gr1.php

0


source






More articles:


All Articles