String to Date conversion loses a second, but only sometimes????

[emartinho]

Hi all,

Got a weird one for you and I haven't been able to find anything on it. Essentially, for some date/time combinations, CDate is "losing" a second!
Here's the output from my testing in the immediate window:Scenario 1: Where'd the second go???

ab1$="9999-12-31 11:08:37"
?ab1$
9999-12-31 11:08:37
?cdate(ab1$)
31/12/9999 11:08:36 '<----- See?! Why is this not 37?

Scenario 2: OK

ab1$="9999-12-31 11:08:31"
?ab1$
9999-12-31 11:08:31
?cdate(ab1$)
31/12/9999 11:08:31

Scenario 3: 1 Second less!

ab1$="9999-12-31 11:08:13"
?ab1$
9999-12-31 11:08:13
?cdate(ab1$)
31/12/9999 11:08:12  '<--- HUH?

Oh, and in case anyone was thinking of telling me to use DateSerial and TimeSerial:
Scenario 1:

?cdate(DateSerial(9999, 12, 31))
31/12/9999 
?cdate(timeSerial(11,08,58))
11:08:58  '<--- Fine here right?
?cdate(DateSerial(9999, 12, 31) + timeSerial(11,08,58))
31/12/9999 11:08:57 '<--- Where did the second go!?

Scenario 2:

?cdate(DateSerial(9999, 12, 31))
31/12/9999 
?cdate(timeSerial(11,08,31))
11:08:31 
?cdate(DateSerial(9999, 12, 31) + timeSerial(11,08,31))
31/12/9999 11:08:31

Scenario 3:

?cdate(DateSerial(9999, 12, 31))
31/12/9999 
?cdate(timeSerial(11,08,13))
11:08:13 
?cdate(DateSerial(9999, 12, 31) + timeSerial(11,08,13))
31/12/9999 11:08:12 '<--- Yep, still missing!

Can anyone shed some light on my addled brain?
Thanks.
-EM

[FireXtol]

The 4th dimension occasionally occurs a second sooner in the year 9999?

Lets be realistic. There's a further distance from now to the year 9999 than there is recorded history. Doesn't seem like a big problem, but a predictable one for a FP type.

I like how if you use the 'date quotes'(#), it automatically 'rounds' the date. And every edit of the line continues to round down until it hits a stable second. Good ol' VB.

[LaVolpe]

Shed some light? Nope. Maybe it has to do with getting near the max of the date variables valid range & double precision?

Here's something to smile about. 1 liner produces incorrect result, but separating them... good results

? cdate(cdbl(cdate("9999-12-31 11:08:37")))
12/31/9999 11:08:36 AM <-- lost second

? cdbl(cdate("9999-12-31 11:08:37"))
2958465.46431713
? cdate( 2958465.46431713)
12/31/9999 11:08:37 AM <-- correct

[emartinho]

The field this relates to is an Expiry Date, and "31/12/9999" is used as "infinity". (hey I didn't tell them to, it was like that when I got here! ) In case you were wondering, the answer to the question of "why would "infinity" have a time value?" is not known to me as well! :P

If it happened with EVERY time value in 31/12/9999, I could buy that there was some threshold/maximum value issue, but some times work, and others don't, as shown in my examples. Sometimes VB can be pretty annoying!

We have found a workaround. Since only hours and minutes can be entered by the user (the values that have seconds were probably generated by other automated processes), and thus seconds are not relevant, the validations that were choking due to the missing seconds will be changed to ignore any seconds values completely. Again, not my call, but in the full scheme of things, it ends up working because to the users, seconds are not visible.

However, if anyone stumbles on an explanation for this, I'm sure everyone would be interested.

Thanks.
-EM

[dilettante]

As Eric's Complete Guide To VT_DATE states:

The range is enormous, and the precision varies greatly over the range! You can represent dates long before the creation of the Universe, though you lose precision as you go.

Of course the next sentence suggests that 31-Dec-9999 is in the range with good precision. Perhaps the problem is that somewhere beyond 00:00:00 on that date the precision has been exceeded.


In any case "no expiration date" is not the same as "expiration date of infinity." So what we have here is a non-programmer trying to program.

This is what Null is for, and why a field like this should be a Variant. The deathly fear of Variants must come from people who have (a.) run into performance problems using Variant data, yet (b.) want a "one size fits every situation solution." The rule they seem to default to is "never use Variants."

This is simply wrong.

The only alternative is to carry the information out of band, i.e. using a separate Boolean value that tells whether the Date value exists or not. This isn't even necessary in VB CSV files (Write#/Input#) which can carry the value #NULL#, let alone most RDBMSs the average VB programmer will encounter. For VB record I/O UDTs can carry a Variant for such nullable-dates just as easily.

Even XML has a Null representation:

<permit> 
    <assignee>Johann Wulf</assignee> 
    <expires xsi:nil="true"/> 
</permit>

[Merri]

This one liner works: Debug.Print CDate(CDbl(CStr(CDbl(CDate("9999-12-31 11:08:37")))))

By coercing floating point to string the floating point rounding error disappears. I assume internally CDate does DATE + TIME calculation, which then results to the same math rounding error that happens with DateSerial + TimeSerial.

Floating point numbers have this kind of issues, doing math with two floating point values will sometimes give different results. Another example, this time all the time using the floating point datatype:

Debug.Print CDate(-Int(CDate("9999-12-31 11:08:37") * -86400) / 86400)
Debug.Print CDate(-Int(CDate("9999-12-31 11:08:37") * -86400) * 1.15740740740741E-05)

Both lines should give the same result (if you use a calculator to do 1 / 86400 you'll get 1.1574074074074074074074074074074e-5) but they do not! The negative Int trick here is a simple "always round up". The later line gives the expected results thanks to using a slightly different math operation.


Anyway, dilettante is correct with Variant in this case. You should just use a NULL field.

[emartinho]

I agaree, if an expiry date is NULL, this means it doesn't expire, and a date should only be saved when there is an actual expiry date. Unfortunately, a NULL field is not an option. This "31/12/9999" logic is used throughout the entire system and with various different data models. Again, not my decision.

An even flakier example of how data is saved here has to do with Times. We have some fields in our DB that contain ONLY time values. They're DATE fields on the DB and when a time is saved, they plunk "31/12/9999" for the date part!

-EM

[LaVolpe]

Strange, I'd expect to see 30 Dec 1899. Maybe some "default value" is in play here? The default value is set to 31/12/9999 00:00:00 and when you update the DB, only the time portion is being added?

[dilettante]

The use of funky dates to mean "no date" burned me badly this week which leaves me feeling a bit sensitive.

Good old legacy data dating to DOS days, an XBase database with data storing dates as packed decimal "Julian" dates (YYYYDDD values). Somtimes you get a 4-bit filler to byte-align this stuff and sometimes you don't because the date is adjacent to another odd-length BCD value.

All of that was bad enough, but some date fields used 0000000 to mean "null" whiles others used 9999999 and in one place 9999000 was the "null" value.


After much hair-pulling where tests kept failing I found that the original code detected these through a "Julian to Gregorian" conversion funcction which turned all illegal values to 99990000 (YYYYMMDD).