Major bug in System.Date.ToOADate and FromOADate

  • Thread starter Thread starter tohear
  • Start date Start date
T

tohear

ToOADate and FromOADate don't handle negative dates with time parts
correctly. Negative dates with no time parts are OK. (Note: In COM
date "zero" is 12/30/1899).

Does anyone know if these are known bugs and if they will be
corrected? In the meantime I strongly recommend writing your own
functions if you work with negative time. In particular if you've
converted a vb6 project to dotnet.

-----------------------

ToOADate converts negative "round" dates ok :

#12/29/1899#.ToOADate ' = -1 OK
#12/28/1899#.ToOADate ' = -2 OK
#12/27/1899#.ToOADate ' = -3 OK

However negative dates with a time part don't convert correctly :

#12/29/1899 11:59:59 PM#.ToOADate ' = ~-2 NOT OK should be ~0
#12/28/1899 11:59:59 PM#.ToOADate ' = ~-3 NOT OK should be ~-1
#12/27/1899 11:59:59 PM#.ToOADate ' = ~-4 NOT OK should be ~-2

Playing around with other times of day it seems this is because the
code in ToOADate is *subtracting* the time part from the negative date
rather than adding it.


System.DateTime.FromOADate converts negative round numbers ok :

FromOADate(-1) ' = #12/29/1899# OK
FromOADate(-2) ' = #12/28/1899# OK
FromOADate(-2) ' = #12/27/1899# OK


However negative dates with a time part don't convert correctly :

FromOADate(-0.999) ' = ~#12/31/1899# NOT OK, should be ~#12/29/1899#.
FromOADate(-1.999) ' = ~#12/30/1899# NOT OK, should be ~#12/28/1899#.
FromOADate(-2.999) ' = ~#12/29/1899# NOT OK, should be ~#12/27/1899#.

Playing around with other numbers it seems this is because the code in
FromOADate is *adding* the fractional part of the number to the date
rather than subtracting it.
 
tohear said:
ToOADate and FromOADate don't handle negative dates with time
parts correctly. Negative dates with no time parts are OK. (Note: In
COM date "zero" is 12/30/1899).

Does anyone know if these are known bugs and if they will be
corrected? In the meantime I strongly recommend writing your own
functions if you work with negative time. In particular if you've
converted a vb6 project to dotnet.

-----------------------

ToOADate converts negative "round" dates ok :

#12/29/1899#.ToOADate ' = -1 OK
#12/28/1899#.ToOADate ' = -2 OK
#12/27/1899#.ToOADate ' = -3 OK

However negative dates with a time part don't convert correctly :
Click to expand...


In VB6, I also misunderstood how it works, and there it worked the same way
as it does now in VB.Net. The simple answer is: It's not a bug, but it's
how it works: :-)
The Integer part is the day based on #12/30/1899#. The fractional part is
the time.

#12/29/1899 11:59:59 PM#.ToOADate ' = ~-2 NOT OK should be ~0
Click to expand...

It returns -1.99998842592593. I think this is important in this case.

-1.99998842592593 has to interpreted this way:
-1 is the day: #12/29/1899# minus 1 day = #12/28/1899#
The fractional part is the time: 0.99998842592593 is 11:59:59 PM

That's also true for the other cases.
#12/28/1899 11:59:59 PM#.ToOADate ' = ~-3 NOT OK should be ~-1
#12/27/1899 11:59:59 PM#.ToOADate ' = ~-4 NOT OK should be ~-2

Playing around with other times of day it seems this is because
the code in ToOADate is *subtracting* the time part from the negative
date rather than adding it.


System.DateTime.FromOADate converts negative round numbers ok :

FromOADate(-1) ' = #12/29/1899# OK
FromOADate(-2) ' = #12/28/1899# OK
FromOADate(-2) ' = #12/27/1899# OK
Click to expand...
^^ probably -3 :-)
However negative dates with a time part don't convert correctly :

FromOADate(-0.999) ' = ~#12/31/1899# NOT OK, should be
~#12/29/1899#. FromOADate(-1.999) ' = ~#12/30/1899# NOT OK, should be
~#12/28/1899#. FromOADate(-2.999) ' = ~#12/29/1899# NOT OK, should be
~#12/27/1899#.

Playing around with other numbers it seems this is because the code
in FromOADate is *adding* the fractional part of the number to the
date rather than subtracting it.
Click to expand...

Well, FromOADate is consistent with ToOADate. The behavior is the same as
explained above.
 
Hello Armin,

I've ran some tests on VB6 and you're correct that it mostly behaves the
same way: 1 second before the end of 12/29/1899 (#12/29/1899 11:59:59
PM#) is in fact considered as 1 second before the start of 12/28/1899
rather than 12/30/1899. So it's "logical" that it should be
-1.99998842592593. I was wrong to believe this was a bug.

However there remains a problem :

in VB6: CDbl(Cdate(-1.5)) = -1.5
and : CDbl(Cdate(-0.5)) = -0.5

In dotnet : FromOADate(-1.5).ToOADate = -1.5
but : FromOADate(-0.5).ToOADate = 0.5 (!!!)

So it's as if doubles smaller than 0 and bigger than -1 are no longer
converted to COM dates properly and instead turned into positive values.

Any suggestions as to how values < 0 and > -1 can still be used?

Thanks,

Tim
 
I think you will find that if you pass actual date/time values you will be
much happier with the results then if you pass things that acre not actually
dates at all?
 
Hello MichKa,

I wouldn't touch OADates with a 10 foot pole if I had the choice.
However the code was originally developed in VB6 so I'm somewhat hostage
to dates becoming doubles that may become dates again, bar a substantial
redesign.

If I've understood this correctly, use of COM dates/times from -23:59:59
to -00:00:01 (which VB6 was perfectly happy with) requires that
To/FromOADate handles the interval ]-1;0[ which it doesn't seem to do.

Would you say that it's by design that FromOADate(-0.5).ToOADate = 0.5
whereas FromOADate(-1.5).ToOADate = -1.5, or is this a bug?
 
Timothy O'Hear said:
Hello Armin,

I've ran some tests on VB6 and you're correct that it mostly behaves
the same way: 1 second before the end of 12/29/1899 (#12/29/1899
11:59:59 PM#) is in fact considered as 1 second before the start of
12/28/1899 rather than 12/30/1899. So it's "logical" that it should
be -1.99998842592593. I was wrong to believe this was a bug.

However there remains a problem :

in VB6: CDbl(Cdate(-1.5)) = -1.5
and : CDbl(Cdate(-0.5)) = -0.5

In dotnet : FromOADate(-1.5).ToOADate = -1.5
but : FromOADate(-0.5).ToOADate = 0.5 (!!!)

So it's as if doubles smaller than 0 and bigger than -1 are no
longer converted to COM dates properly and instead turned into
positive values.

Any suggestions as to how values < 0 and > -1 can still be used?
Click to expand...


-0.5 and +0.5 are equal because -0 and +0 are equal. If the day is zero, it
is #12/30/1899#. Then add the fractional part, i.e. #12:00#. Result:
#12/30/1899 12:00#
 
That is by design. I was about to explain why when I noticed that Armin did
it already.
 
Back
Top