This is a bug in the to_char funtion used on dates.
Do you think Oracle will fix this?
This is one example there are several.
Run this against your test database and see format mask iyyy gives a wrong year, where as yyyy ofcause gives the right
select to_char(snapshot,'iyyy')iformated_year,
to_char(snapshot,'yyyy')yfformatedyear,
to_char(snapshot,'ww')uge,
to_char(snapshot,'Iw')iuge,
to_char(snapshot,'dy')ge,
snapshot
from (
select to_date('31/12/2018 02.00','dd/mm/yyyy hh24.mi') snapshot
from dual
);We operate the system, where we discowered this problem. We are not allowed to change any code in the production system
This works exactly as intended. There is no bug here.
The format IYYY returns the ISO year and is typically used in combination with the ISO week.
ISO weeks start on Monday. The first week of the ISO year is the week containing 4th January. There will be a difference between the ISO and calendar years for some days unless 1st Jan for that year is a Monday.
If 1st Jan falls Tue-Thu, the end of Dec will be in week 1 of the next ISO year.
If 1st Jan falls Fri-Sun, the start of Jan will be in the last week the previous ISO year.
https://en.wikipedia.org/wiki/ISO_week_date If this is not the behaviour you want, you'll need to update the code.