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

Answer 2

Probably you want

update your_table
set this_date = add_months(this_date, 5)
where ...

This will add five months to the selected dates.

---

Your edited question says you want to update all the dates to the current month and year; you can automate it like this ...

update your_table
set this_date = add_months(this_date, 
                        months_between(trunc(sysdate,'mm'), trunc(this_date, 'mm')))
-- or whatever filter you require
where this_date between trunc(sysdate, 'yyyy') and sysdate 
/

Using month_between() guarantees that you won't get invalid dates such as '2017-11-31'. You say in a comment that all the dates will be < 05.mm.yyyy but your sample data disagrees. Personally I'd go with a solution that doesn't run the risk of data integrity issues, because the state of your data tomorrow may will be different from its state today.

Check out the LiveSQL demo.

Answer 3

I would start off with something like this to get my dates and then craft an update from it (substitute old_date with your date column and source_table with the table name):

select old_date, to_char(sysdate, 'YYYY-MM-') || to_char(old_date, 'DD') from source_table;