How to update year only in date format dd-MON-yyyy use Oracle sql plus-CodePudding

DOB  data type : DATE

13-JAN-76
10-FEB-80
17-MAR-79

---------------
Expected output

13-JAN-04
10-FEB-04
17-MAR-04

I tried use this but failed.

update table set dob=to_date(dob,'dd-MON-yyyy')||','||'2004','dd-MON-yyyy') where id='1001';

date format not recognized.

anyone help is much appreciated.

CodePudding user response：

Here's one option:

(just to know date format; you don't have to do that)

SQL> alter session set nls_date_format = 'dd.mm.yyyy';

Session altered.

Here it goes:

SQL> select * from test;

DOB
----------
13.01.1976
10.02.1980
17.03.1979

SQL> update test set
  2    dob = add_months(dob, (2004 - extract(year from dob)) * 12);

3 rows updated.

SQL> select * from test;

DOB
----------
13.01.2004
10.02.2004
17.03.2004

SQL>

CodePudding user response：

You update statement relies on the session default date format. Typically this is defined by session parameter NLS_DATE_FORMAT

Better specify it explicitly:

update table set dob = to_date(2004 || TO_CHAR('dob', '-MM-DD'), 'YYYY-MM-DD')