Solved

SQL weeknumber issue: wrong weeknumber

  • 26 March 2021
  • 6 replies
  • 253 views
B
Rank
Userlevel 4
Badge +9

Hi, 

I am trying to extract the weeknumber from a date. But both my SQL statements return the wrong weeknumber. Here is my first statement: 

SELECT to_char(to_date(cf$_co_date_order, 'dd-mm-yyyy'), 'ww') FROM customer_order_cfv

Statement returns: 

22-3-2021 0:00 12
23-3-2021 0:00 12
24-3-2021 0:00 12
24-3-2021 0:00 12
25-3-2021 0:00 12
26-3-2021 0:00 13
26-3-2021 0:00 13
26-3-2021 0:00 13
26-3-2021 0:00 13

 

My second statement: 

SELECT to_char(to_date(cf$_co_date_order, 'dd-mm-yyyy'), 'iw') FROM customer_order_cfv

Statement returns: 

22-3-2021 0:00 12
23-3-2021 0:00 12
24-3-2021 0:00 13
24-3-2021 0:00 13
25-3-2021 0:00 13
26-3-2021 0:00 13
26-3-2021 0:00 13
26-3-2021 0:00 13
26-3-2021 0:00

13

 

So both are wrong. I expect every date to return weeknumber 12, because that is the correct weeknumber. Does someone know how to achieve this? 

icon

Best answer by EntNadeeL 29 March 2021, 13:41

View original
Kind regards, Simon
This topic has been closed for comments

6 replies

O
Rank
Userlevel 3
Badge +5

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.

B
Rank
Userlevel 4
Badge +9

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: 

22-3-2021 0:00 12
23-3-2021 0:00 12
24-3-2021 0:00 13
24-3-2021 0:00 13
25-3-2021 0:00 13
26-3-2021 0:00 13

 

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

Kind regards, Simon
EntNadeeL
Rank
Userlevel 5
Badge +9

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 

 

Nadeesha Liyanage - https://www.linkedin.com/in/nadeeshaliyanage/
O
Rank
Userlevel 3
Badge +5

Hi @ola.nissen ,

Thank you for your reply. …

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

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.

B
Rank
Userlevel 4
Badge +9

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. 

22-3-2021 0:00 12
23-3-2021 0:00 12
24-3-2021 0:00 12
24-3-2021 0:00 12
25-3-2021 0:00 12
26-3-2021 0:00 12

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

Kind regards, Simon
EntNadeeL
Rank
Userlevel 5
Badge +9

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. 

22-3-2021 0:00 12
23-3-2021 0:00 12
24-3-2021 0:00 12
24-3-2021 0:00 12
25-3-2021 0:00 12
26-3-2021 0:00 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 :)

Nadeesha Liyanage - https://www.linkedin.com/in/nadeeshaliyanage/
Terms & ConditionsCookie settings