CODEWITHSUNDEEP
sqloracle-database
Piloumpicou
Piloumpicou

Reputation: 67

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

I have a column in my table which contains DATEs and I need to keep only the YEAR (=> in a NUMBER or INT type)

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 get this error :

single-row subquery returns more than one row

Could you help?!?

Upvotes: 3

Views: 6169

Answers (5)

Deepesh kumar Gupta
Deepesh kumar Gupta

Reputation: 896

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

Upvotes: 2

Gary_W
Gary_W

Reputation: 10360

This looks like a perfect situation in which to use a virtual column if you are on at least 11g. Consider this create table statement for my test table, where v_year is the 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! the year is in there automagically! No special updating needed.

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>

Some more info and examples: http://oracle-base.com/articles/11g/virtual-columns-11gr1.php

Upvotes: 1

Lalit Kumar B
Lalit Kumar B

Reputation: 49062

It is a bad idea to do that. Storing only a portion of date would hardly be of any use. Also, you won't be able to do any date arithmatic on the column.

To fetch the year value Oracle provides the datetime format for display. For any date calculation based on year you just need to do:

EXTRACT(YEAR FROM date_column)

If you still want to change the design and store only the date part, then instead of creating a static column, use a VIRTUAL 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.

Upvotes: 2

MT0
MT0

Reputation: 167774

You can use the EXTRACT function:

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

Upvotes: 2

Radu Gheorghiu
Radu Gheorghiu

Reputation: 20489

You need to change your query to:

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

This SELECT statement will return all of the rows in the table, from column oldColumn.year, which generates your "single-row subquery returns more than one row" error.

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

Upvotes: 3

Related Questions