Major bug in System.Date.ToOADate and FromOADate

[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.

[Armin Zingler]

"tohear" <ti****@ohear.com> schrieb

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 :

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
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 ^^ 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.

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

[Armin Zingler]

"tohear" <ti****@ohear.com> schrieb

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 :

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
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 ^^ 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.

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

[Timothy O'Hear]

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

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Timothy O'Hear]

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

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Michael \(michka\) Kaplan [MS]]

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?
--
MichKa [MS]

This posting is provided "AS IS" with
no warranties, and confers no rights.
"Timothy O'Hear" <ti****@ohear.com> wrote in message
news:ON**************@tk2msftngp13.phx.gbl...

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

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Timothy O'Hear]

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?
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Armin Zingler]

"Timothy O'Hear" <ti****@ohear.com> schrieb

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?

-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#
--
Armin

[Michael \(michka\) Kaplan [MS]]

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?
--
MichKa [MS]

This posting is provided "AS IS" with
no warranties, and confers no rights.
"Timothy O'Hear" <ti****@ohear.com> wrote in message
news:ON**************@tk2msftngp13.phx.gbl...

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

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Timothy O'Hear]

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?
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Armin Zingler]

"Timothy O'Hear" <ti****@ohear.com> schrieb

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?

-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#
--
Armin

[Michael \(michka\) Kaplan [MS]]

That is by design. I was about to explain why when I noticed that Armin did
it already.
--
MichKa [MS]

This posting is provided "AS IS" with
no warranties, and confers no rights.
"Timothy O'Hear" <ti****@ohear.com> wrote in message
news:u%***************@TK2MSFTNGP11.phx.gbl...

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?
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!