Itgendid289 dates substraction inside variables

Questions
1

It seems some sort of deviation in recent releases of Invantive UniversalSql prevent to manipulate dates inside variables:

itgendid289:
The value ‘11/26/2025 11:12:34’ is not a valid decimal.
Le format de la chaîne d’entrée est incorrect.
Use decimal dot ‘.’ as a decimal separator and comma ‘,’ as a thousands separator.

declare
   l_test     integer := 0;
   t0         datetime := sysdateutc;
   t1         datetime := sysdateutc;
   secs0      decimal;
   secs1      decimal;
begin
    for i in 1..500000 
    loop
      l_test := l_test + 1;
      --dbms_output.put_line(l_test);
    end loop;
    --
    t1:= sysdateutc;
    dbms_output.put_line('t0: ' || t0);
    dbms_output.put_line('t1: ' || t1);
    secs0 := sysdateutc - sysdateutc; -- this works
    dbms_output.put_line(secs0);
    secs1:= round(t1 - t0); -- this triggers itgendid289
end;
2

The problem does not seem to reproduce on Invantive Cloud 25.0.108, which has the same SQL engine as Query Tool 25.0.108:

t0: 26-11-2025 21:26:52
t1: 26-11-2025 21:26:54
-0.00000000000462962962962963
0

Last zero was added by adding a dbms_output of secs1.

Can you please upgrade to 25.0.108 or newer, and retry?

3

We confirm that upgrading from 25.0.95 (current .msi release) to 25.0.108 (downloadable from release notes page) solve the issue.

4

OK, thanks for the update! The bug was not known, but probably some regression test case triggered the failure and has been resolved since the release of 25.0.95.

5

the issue is reproduced again! In fact its located in the variable declaration :

[Datetime] instead of [datetime]

to reproduce

declare
   l_test     integer := 0;
   t0         Datetime := sysdateutc;
-- using [Datetime] instead of [datetime] raise the issue
   t1         Datetime := sysdateutc;
   secs0      decimal;
   secs1      decimal;
begin
    for i in 1..500000 
    loop
      l_test := l_test + 1;
      --dbms_output.put_line(l_test);
    end loop;
    --
    t1:= sysdateutc;
    dbms_output.put_line('t0: ' || t0);
    dbms_output.put_line('t1: ' || t1);
    secs0 := sysdateutc - sysdateutc; -- this works
    dbms_output.put_line(secs0);
    secs1:= round(t1 - t0); -- this triggers itgendid289
end;
6

That is a cool one to discover! The problem reproduces on:

declare
  --
  -- Change datatype from 'datetime' to 'Datetime' of at least
  -- one declaration to trigger itgendid289.
  --
  l_t0         Datetime := to_date('20251101', 'YYYYMMDD');
  l_t1         datetime := to_date('20251101', 'YYYYMMDD');
  l_x          decimal;
begin
  -- 
  -- Triggers itgendid289:
  -- The value '11/27/2025 13:37:07' is not a valid decimal. 
  -- The input string '11/27/2025 13:37:07' was not in a correct format.
  --
  l_x := round(l_t1 - l_t0);
end;

We will try to solve it.

8

A new release has been made available that should solve this issue. The necessary changes are available with all releases of 25.0.119 and later, and with all future BETA versions.

This new release has been/will be taken into production on Invantive Cloud.

For more information see: