Using criteria on db Date-Time field 2

[CP60]

What is the best way do search on db date-time field using date-time criteria and getting the expected results.

Example: I have an sql statement written in VB6 and say I am using a Mdb database and searching on a datetime field.

As date-time criteria I am using 2007-11-28 11:29:15

How should I apply the criteria in the sql statement:

"... Where SomeDateField Between " & "#2007-11-28 11:29:14#" & " AND " & "#2007-11-28 11:29:16#"

or maybe

"... Where CSNG(SomeDateField) = CSNG(" & "#2007-11-28 11:29:14#" & ")"

Or ??

I want to avoid problems with the decimal portion of the datetime not representing the time precisely.

 

[tedsmith]

It looks to me that the two records only appear identical because they are both being presented to you in your local area format, However in the basic level of the database, they must be stored formatted differently in some way and the Jet is doing a conmversion string search.

Maybe it might even be a case of the original entries were entered as "mm-dd-yyyy" vs "d/m/yyyy"
I am not in US and regardless of how I enter the date and time, CVDate reformats it to my local area format "dd-mm-yyyy hh:nn:ss" even if I enter the time before the date instead of afterwards or enter in US format.

Maybe some older records were entered on a computer with a different or incorrect area format setting?

If this is the case then it looks as though you need expressions in the criteria to intercept the illegal formatted records (slow) or if you really need the speed, execute a correction routine to change every record to the your local format.

 

[CP60]

I think the values just somehow got stored with a larger precision than 10 decimal places

 

[strongm]

ted,

here's an extreme example of the sort of thing that is going on (note that technically the CDates here are superflouos, but I've included them to show that explicity converting to a date makes no difference):

Dim MyDate As Date
Dim CompareDate As Date
Dim lp As Long

CompareDate = "00:00:00"
For lp = 1 To 24& * 60 * 60
MyDate = CDate(DateAdd("s", lp, 0))
CompareDate = CDate(DateAdd("s", 1, CompareDate))
If CompareDate <> MyDate Then
Debug.Print lp, CompareDate, MyDate
End If
Next

 

[CP60]

Nice. And good to know.
Yes, this sort of demonstration explains what probably is really going on.

 

[tedsmith]

I may be a bit thick but this demonstration shows very well WHAT is going on but does not EXPLAIN it to me.

So what exactly IS going on?

 

[strongm]

I'm using two methods of generating the same date/time. As a result there are differences in the internal representation of that date time. Since it is actually that internal representation that gets compared we get match failures. A LOT of match failures

 

[CP60]

This will show an approximate difference of the two values

Debug.Print lp, CDec(CompareDate), CDec(MyDate), "Difference: " & CDbl(CompareDate) - CDbl(MyDate)

 

[tedsmith]

Yes but WHY?
So what determines how a date is stored and why would some be of one type and the other different if the field is set to Date/Time format?
I would have though that if the data was stored and read by the same app then all date entries would be of the same type. Why would they be mixed?

What determines the type of date data that will be stored in any given database?

 

[strongm]

>how a date is stored

We've already told you that. And CP60 has hinted at what is going on, and the illustration should have helped explain the why.

Essentially an double (which is how a VB(A) date is actually stored both in memory and in Access) is actually far more accurate than a date actually requires. As a result various DIFFERENT double values can represent the SAME date. And different ways of calculating or constructing (including some implicit internal transformations) those same dates will often result in different internal values (differing somewhere around about the 10th decimal place or beyond).

But the comparisons are done against the internal representations, not against the output formats that dates are displayed in.

Microsoft do have KB articles that touch on this. For example, near the end of

http://support.microsoft.com/kb/210276

 

[tedsmith]

Thanks, now I can see the reason why.
But what circumstances could have led to date data in the same column of the same database ending up with different precisions of dates?
Is it because different programs were used to update the same column or is it a random thing that the same program can store slightly different double values or perhaps a lack of precision in the date conversion algorithm?

 

[strongm]

>what circumstances could have led to date data in the same column of the same database ending up with different precisions of dates?

Eh? The OP is comparing date literals against date data fro the db. There is no indication of how the data data was actually created, nor that we are comparing two different date data from the same column. So surely this question is not relevant.