0

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

Improve this question
8
  • 4
    If you don't like either the WW or the IW definition of "week", can you explain exactly how you define a week? Whatever definition of "week" you are using, it is almost certainly possible to define a SQL statement that would do what you want. But it will almost certainly be easier to define your own calendar table that lets you map dates to weeks and just join to that. That will be vastly easier than trying to shoehorn that logic into every report that will inevitably want to make this conversion. Commented Apr 15 at 6:43
  • Please provide some sample input data and the expected result. Commented Apr 15 at 7:55
  • For custom behaviour, create a lookup table. As per @JustinCave suggestion of a calendar table. Commented Apr 15 at 8:43
  • Ok but I don't understand the reason why 30. and 31 should fall in 2 different weeknumber if they are in the same week. I don't define anything. I have table the yyyy-ww is harrdcode and there is value for 2025-53. So I would expect that both dates converted with to_char will have the same yyyy-ww as 2025-53 because they are in the same week.This is simple conversion nothing else. Commented Apr 15 at 10:10
  • 1
    @Pato Why do you think they are in the same week? Please explain how you define a week Commented Apr 15 at 10:41

1 Answer 1

Reset to default
0

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

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

yes but that is confusing 312-12-2025 is 2025-01 but i would expect 2026-01. nevermind I did itnother way with our time dimension and join on particular date

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.