in the calendar I see both 30th and 31st of Dec in years 2025 and 2026 fall in the same week but using the to_char function to create year-week field I will get different weeks
select
to_char(to_date('30-12-2025','dd-mm-yyyy'),'yyyy-ww')
, to_char(to_date('31-12-2025','dd-mm-yyyy'),'yyyy-ww')
, to_char(to_date('30-12-2026','dd-mm-yyyy'),'yyyy-ww')
, to_char(to_date('31-12-2026','dd-mm-yyyy'),'yyyy-ww')
from dual;
We have orders in database with end_date 30-12-2025 and sometimes 31-12-2025. the issue could be that in another table there are some other data on week's bases like 2025-01 till 2025-53. If I want to join them it could happen that the order ending on 30-12-2025 will not join with 2025-53 but actually the date is week 53.
Why is the behavior in Oracle such that?
I cannot use ISO week because for year 2026 and 31st Dec it will be 2026-53 as expected but not for 31st Dec 2025, because it will be 2025-01 which is incorrect.
I checked the NLS in the settings and tried Date language German, English, Format dd-mm-rr or dd-mm-yyyy. still the same
The main issue is to define the start of the week - some like it to be sunday, other see it on monday - or it could be the first day of year.
That is exactly what makes you problems with To_Char() function:
1. WW parameter - First day of week is the day that happens on the first day of year - so it could be any day (Sun, Mon, Tue ,... any) and that defines braking day for the whole year
2. IW parameter - The first day of the week is always Monday, BUT - if the year ends with less than half of week (starting on Monday) in that particular year then the week will be 01 (most of it is in new year) until the first Monday next year when it will turn into 02. If more then half of the week is in ending year then the week will be 53 till the next Monday when it will be 01.
See the fiddle