0
select floor(to_number(to_date('20180620130000','yyyy-mm-dd hh24:mi:ss')-to_date('20180620080000', 'yyyy-mm-dd hh24:mi:ss'))*24*60)  
from dual;

select  to_number(to_date('20180620130000','yyyy-mm-dd hh24:mi:ss')-to_date('20180620080000', 'yyyy-mm-dd hh24:mi:ss'))*24*60  
from dual;

Why does the SQL execution using floor result in 299, it should be 300

yang
  • 3
  • 1
  • 2
    Unrelated, but: the `to_number()` is useless (actually it's a bug waiting to happen) as subtracting one date from another will already return a number. There is no need to convert that number to a varchar and back to a number (which is what `to_number()` will do) –  Jun 24 '19 at 07:19
  • 2
    @a_horse_with_no_name `to_number` is **related** remove it and you get the correct result. – Marmite Bomber Jun 24 '19 at 07:25
  • yes, you're right – yang Jun 24 '19 at 08:10

1 Answers1

2

You are encountering some strange precision problem.

But the solution is simple - remove the superfluous TO_NUMBER (the difference of two DATEs is a number) and you get the correct result

select 
floor((to_date('20180620130000','yyyy-mm-dd hh24:mi:ss')-to_date('20180620080000', 'yyyy-mm-dd hh24:mi:ss'))*24*60)  flr 
from dual;

       FLR
----------
       300
Marmite Bomber
  • 19,886
  • 4
  • 26
  • 53
  • @yang yes, its funny, the exact reason is not clear to me;) – Marmite Bomber Jun 24 '19 at 08:02
  • 1
    The result of the date subtraction isn't a normal (type-2) number; dumping it shows an internal (undocumented, as far as I can see) data type 14, which Oracle converts to other types as needed. It seems to hold the exact number of seconds, not a fraction at that point. By implicitly converting to a string and back to a normal number the datatype changes and the result cannot be represented exactly. I found [this investigation of the internal structure](https://bennytu.wordpress.com/2012/02/19/subtracting-dates-in-oracle-how-it-is-stored-internally/), which saved me some experimentation *8-) – Alex Poole Jun 24 '19 at 08:31
  • yes, The result of date - date is 0.208333333333333. select (0.208333333333333 * 24 * 60) as to_num ,floor(0.208333333333333 * 24 * 60) as flr from dual; the result is 300 and 299. – yang Jun 24 '19 at 08:42
  • @yang - (a) the result of of data-date is *presented* to you (or your client) as a number - see my previous comment; and (b) your `to_num` value is not 300, it is 299.9999999999995, but your client is rounding it for display. If you are using SQL\*Plus or SQL Developer, do `set numwidth 17` and try that again. – Alex Poole Jun 24 '19 at 09:16
  • I got it , to_char also reflects what you said, thank you – yang Jun 24 '19 at 09:50