UPDATE month and year to current but leave day

Question

I have situation in Oracle DB where I need to UPDATE every month some dates in table following this condition:

1) If date in table like '03.06.2017' UPDATE to '03.11.2017'

2) If date in table like '29.06.2016' UPDATE to '29.11.2017'

2) If date in table like '15.02.2016' UPDATE to '15.11.2017'

So basically always UPDATE part of date(month, year) to current month/year but always leave day as it is.

Edit:

It will be all months from 1-12 not only June. I need to do something like this... UPDATE table SET date = xx.(month from sysdate).(year from sysdate) WHERE... xx (day) leave as it is in DB.

Br.

Answer 1

You can use MONTHS_BETWEEN to determine how many months you need to add and then use the ADD_MONTHS function:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE dates ( value ) AS
SELECT DATE '2017-06-03' FROM DUAL UNION ALL
SELECT DATE '2016-06-29' FROM DUAL UNION ALL
SELECT DATE '2016-02-15' FROM DUAL UNION ALL
SELECT DATE '2016-03-31' FROM DUAL;

Update:

UPDATE dates
SET value = ADD_MONTHS(
              value,
              CEIL( MONTHS_BETWEEN( TRUNC( SYSDATE, 'MM' ), value ) )
            );

Query 1:

SELECT * FROM dates

Results:

|                VALUE |
|----------------------|
| 2017-11-03T00:00:00Z |
| 2017-11-29T00:00:00Z |
| 2017-11-15T00:00:00Z |
| 2017-11-30T00:00:00Z | -- There are not 31 days in November