You have a date where the month is only represented by a single digit, while in your date mask you have MM indicating that you expect two digits. Can this be the problem?

Edit: Did a quick test actually running your statement, and I see two problems. First, your date mask doesn’t account for the time portion of your value (I assume that 22-03-2021 0:00 is your text string in your data). When running that statement in SQL Fiddle I get the following error

SELECT to_char(to_date('22-3-2021 0:00', 'DD-MM-YYYY'), 'iw') FROM dual;

ORA-01830: date format picture ends before converting entire input string

If I change to include the time portion, I get the following error

SELECT to_char(to_date('22-3-2021 0:00', 'DD-MM-YYYY HH:MI'), 'iw') FROM dual

ORA-01849: hour must be between 1 and 12

If I change to 

SELECT to_char(to_date(substr('22-3-2021 0:00',1,9), 'DD-MM-YYYY'), 'iw') FROM dual

removing the time portion in the input to to_date, and using iw (internal week) I get the correct result for the dates mentioned. So it had nothing to do with the month being represented by one or two digits, my guess was wrong.

Hi @ola.nissen ,

Thank you for your reply. I also tested your statement in SQL Fiddle and indeed it returns the correct weeknumber. 

But when I run the statement in IFS like this:

SELECT to_char(to_date(substr(cf$_co_date_order,1,9), 'DD-MM-YYYY'), 'iw') FROM customer_order_cfv 

I still get the wrong results: 

Do you know what I am doing wrong? I expected the statement to return weeknumber 12 for all the dates above. 

Hi @BZNSIMON ,

Your string manipulation is bit wrong.

SELECT to_char(to_date(substr(cf$_co_date_order,1,10), 'DD-MM-YYYY'), 'iw') FROM customer_order_cfv 

Sorry, I’m out of ideas now.

As to the comment from EntNadeeL I based my substr on the data that you showed, where you month number was a one digit number. Ideally the data that you have (as strings?) should be formatted in the same style so a consistent approach to string-handling can be applied. If you have strings like “1-1-2021 0:00” and “31-12-2021 10:10” it will be harder to write functions to handle those strings.

Hi @EntNadeeL  and @ola.nissen , 

Thank you both for your replies and your help. @EntNadeeL I tried your suggestion, but this didn’t change the outcome. But instead of 10 I tried 8, and then I got my desired result, see below, all date's returned weeknumber 12. 

SELECT to_char(to_date(substr(cf$_co_date_order,1,8), 'DD-MM-YYYY'), 'iw') FROM customer_order_cfv

Yes it should be something with substr. Because previously it was leaving one character and 10 worked for me. 

13 week is supposed to start on 28th. it gave me the right results.

somehow good that it worked :)