SQL weeknumber issue: wrong weeknumber

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