Itgendid289 dates substraction inside variables

Vragen (en)
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

Het probleem is opnieuw gereproduceerd! Het bevindt zich eigenlijk in de variabeledeclaratie:

[Datetime] in plaats van [datetime]

om te reproduceren

declare
   l_test     integer := 0;
   t0         Datetime := sysdateutc;
-- het gebruik van [Datetime] in plaats van [datetime] veroorzaakt het probleem
   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; -- dit werkt
    dbms_output.put_line(secs0);
    secs1:= round(t1 - t0); -- dit veroorzaakt 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:

9

Deze vraag is automatisch gesloten na minstens 1 week inactiviteit nadat een mogelijke oplossing was voorgesteld. Het laatst gegeven antwoord is als oplossing gemarkeerd.

Stel een nieuwe vraag via een apart topic als het probleem opnieuw optreedt. Voeg in de nieuwe vraag een link naar dit topic toe door de URL ervan in de tekst te plakken.