Number multiplied by constant results in a negative number

user_235671 0
Viewed 59

I'm trying to calculate the date for a corresponding week-of-year one year ago. It seems to be working now but one thing botters me that initially lead to a couple of errors that I fixed by experimenting with various methods.

The issue is that when I don't use to_number with &weekOfYearNow - &weekOfYearThen and use this result to calculate the lastYearFixed then the result will be -311 instead of 7. Why is this so? Aren't that two variables (weekOfYearNow & weekOfYearThen) already numbers?

define today = to_date('30/12/20', 'dd/mm/yy');
define lastYear = add_months(&today, -12);

select &today as "Today" from sys.dual;
select &lastYear as "LastYear" from sys.dual;

define weekOfYearNow = to_number(to_char(&today, 'ww'));
define weekOfYearThen = to_number(to_char(&lastYear, 'ww'));
define weekOfYearOffset = to_number(&weekOfYearNow - &weekOfYearThen); -- <-- must use to_number - but why?

define lastYearFixed = &lastYear + (&weekOfYearOffset * 7); -- <-- this equals -311 if I don't call to_number above

define monday = add_months(sysdate - 7 - to_char(sysdate, 'd') + 2, -12);
define sunday = add_months(sysdate - 7 + to_char(sysdate, 'd'), -12);

select &weekOfYearNow as "WeekOfYearNow" from sys.dual;
select &weekOfYearThen as "WeekOfYearThen" from sys.dual;
select &weekOfYearOffset as "WeekOfYearOffset" from sys.dual;
select &lastYearFixed as "LastYearFixed" from sys.dual;
oracle-sqldeveloper operator-precedence oracle sql
2 Answers

You don't need (and shouldn't use) to_number(); but you do need to enclose the first expression in parentheses, either as:

define weekOfYearOffset = (&weekOfYearNow - &weekOfYearThen);
define lastYearFixed = &lastYear + (&weekOfYearOffset * 7);

or

define weekOfYearOffset = &weekOfYearNow - &weekOfYearThen;
define lastYearFixed = &lastYear + ((&weekOfYearOffset) * 7);

Without either of those, when the substitution is expanded you effectively get:

define lastYearFixed = &lastYear + (&weekOfYearNow - &weekOfYearThen * 7);

(It actually has all those terms expanded to show the nested to_date() and to_number() etc. calls, but for brevity this gives the idea; the point is they are not yet numbers, and no conversions or calculations have been done yet. You can set verify on to see the full glory/horror.)

The operator precedence has * as a higher precedence than (binary) -, which means that is equivalent to:

define lastYearFixed = &lastYear + (&weekOfYearNow - (&weekOfYearThen * 7));

So you're adding weekOfYearNow and subtracting 7 times weekOfYearThen; rather than adding 7 times the difference between them.

In numbers:

select 53 - 52 * 7 as a,
       53 - (52 * 7) as b,
       (53 - 52) * 7 as c
from dual;

    A     B     C
----- ----- -----
 -311  -311     7

When you use to_number() you are overring the default precedence, but with an unnecessary implicit conversion to a string and explicit conversion back to a number. Just the parentheses are enough to override the default behaviour.

user_2663040

I was going to say I bet it's a SQL/Plus thing (and Alex proved that, thanks Alex) as when I converted it to a PL/SQL anonymous block and ran it in Toad it worked as expected. I was not going to post this as it is not really an answer, but then it's kind of interesting to compare the languages and note gotcha's that could bite you that one should keep in mind so I'll leave it for the sake of learning.

SET serveroutput ON

DECLARE
  today DATE;
  lastyear DATE;
  weekOfYearNow NUMBER;
  weekOfYearThen NUMBER;
  weekOfYearOffset NUMBER;
  lastYearFixed DATE;
  monday DATE;
  sunday DATE;
BEGIN
 today := TO_DATE('30/12/20', 'dd/mm/yy');
 lastYear := ADD_MONTHS(today, -12);

 DBMS_OUTPUT.PUT_LINE('Today: ' || today);
 DBMS_OUTPUT.PUT_LINE('LastYear: ' || lastYear);

 weekOfYearNow    := TO_NUMBER(TO_CHAR(today, 'ww'));
 weekOfYearThen   := TO_NUMBER(TO_CHAR(lastYear, 'ww'));
 weekOfYearOffset := weekOfYearNow - weekOfYearThen; -- <-- must use to_number - but why?

 lastYearFixed := lastYear + (weekOfYearOffset * 7); -- <-- this equals -311 if I don't call to_number above

 monday := ADD_MONTHS(SYSDATE - 7 - TO_CHAR(SYSDATE, 'd') + 2, -12);
 sunday := ADD_MONTHS(SYSDATE - 7 + TO_CHAR(SYSDATE, 'd'), -12);

 DBMS_OUTPUT.PUT_LINE('WeekOfYearNow: ' || WeekOfYearNow);
 DBMS_OUTPUT.PUT_LINE('WeekOfYearThen: ' || WeekOfYearThen);
 DBMS_OUTPUT.PUT_LINE('WeekOfYearOffset: ' || WeekOfYearOffset);
 DBMS_OUTPUT.PUT_LINE('LastYearFixed: ' || LastYearFixed);

END;

Today: 30-DEC-20
LastYear: 30-DEC-19
WeekOfYearNow: 53
WeekOfYearThen: 52
WeekOfYearOffset: 1
LastYearFixed: 06-JAN-20
user_25434160
Related