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.

 

[Andrzejek]

Did you try:
[tt]

"... Where SomeDateField Between #2007-11-28 11:29:14# AND #2007-11-28 11:29:16#"
[/tt]

Have fun.

---- Andy

 

[gmmastros]

The first version is best. Let me explain...

If there is an index on the SomeDateField column, your database engine will be able to use an index to quickly return the first match and the last match and then return everything in between. The version that uses CSNG will cause the database engine to examine every row in the table to determine if it matches the result because it needs to apply the function to all of the data in the column first.

This concept is called "[google]sargable[/google]". I strongly encourage you to spend a couple minutes researching this. You'll see that it confirms what I already said. If your table is small (small number of rows), you may not notice a performance difference between the various methods, but with bigger tables (and the presence of an index), you will see that the sargable version of the query performs many times faster.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[CP60]

Andrzejek, I already mentioned that in the OP.

gmmastros, Thanks. But I guess I was expecting to find more than what can really be done here.

As mentioned in the OP: "best way...getting the expected results" and "I want to avoid problems with the decimal portion of the datetime not representing the time precisely."

I think it would also be slow altogether to use BETWEEN when several records could be returned and therefore need to use a slower non-ForwardOnly recordset cursor, move through the returned records, convert to a datatype and compare results in order to find the record.

I am also not sure if using CSNG() is always a sure thing which will always find a specific piece of data.

I was sort of hoping I had overlooked something (therefore the two methods mentioned) and that this dbms would be ables to handle this on it's own, finding the single record every time, with out there being too much of a drastic difference in the time it takes, or having to loop through records.

I think best would be something like using the BETWEEN and then use data conversion and comparison on the results, all within the same stored proceedure ran by the dbms. But this isn't possible with an MDB.

Thanks anyways.

 

[gmmastros]

Why not do both? You can combine both your methods.

[tt]
Where SomeDateField Between " & "#2007-11-28 11:29:14#" & " AND " & "#2007-11-28 11:29:16#"
And CSNG(SomeDateField) = CSNG(" & "#2007-11-28 11:29:14#" & ")"
[/tt]

When the database gets the query, it will use the between criteria to quickly narrow down the result set and then use the slower additional criteria to narrow it down further.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Andrzejek]

Sorry, I misunderstood your post. My fault.

But to clarify, are you trying to bypass the time portion of your data and go after the date part only? Something to the effect of
[tt]
"... Where SomeDateField Between #2007-11-28# AND #2007-11-28#"
[/tt]
Hence "I want to avoid problems with the decimal portion of the datetime not representing the time precisely."



Have fun.

---- Andy

 

[CP60]

gmmastros, I never thought about doing that because I was assumming it would do both "at the same time".
That is, because of the function being included, it would inspect each row/record one at a time.
Maybe I am just not giving the Jet dbms enough credit here and assumming otherwise.

There is a method to investigate the JET's query plan and I will have a look to see if it can and will confirm this.

If it is true what you say, then that would be fantastic.

Andrzejek, Both: Date and Time part. Using criteria on the exact time part where the a problem lies (understandable though).

 

[gmmastros]

CP60,

Admittedly, I don't have a lot of experience with Access. I know with certainty that this would work in SQL Server so it is reasonable to assume (and then test) that it would also work in Access.

Please let us know what you discover.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[CP60]

Will try it using a datetime search between seconds 14 and 16 and an exact search on the single conversion of the datetime using second 15:

Where SomeDateField Between " & "#2007-11-28 11:29:14#" & " AND " & "#2007-11-28 11:29:16#"
And CSNG(SomeDateField) = CSNG(" & "#2007-11-28 11:29:15#" & ")"

 

[CP60]

The JET Query Plan shows the following:

--- temp query ---

- Inputs to Query -
Table 'SomeTable'
- End inputs to Query -

01) Restrict rows of table SomeTable
      using rushmore
      for expression "SomeDateField Between #2007-11-28 11:29:14# AND #2007-11-28 11:29:16#"
      then test expression "CSNG(SomeDateField)="

So, that did indeed work the way you said it would, gmmastros.

However, I have found that the CSng() is still way too fuzzy:
-Using just CSNG() 141 rows all ranging within 3 seconds
-Using BETWEEN and CSNG I get 3 rows).

However, I then thought "VBA-Jet", and changed it to use TimeValue() (surprise) and that reduced it down to the exact time.

As you said, using just an expression, CSNG(), TIMEVALUE(), etc., would be too slow and increase network load/traffic excessively because the indexes couldn't be taken advantage of.

Thank you

 

[gmmastros]

I'm glad I was able to help. I'm also grateful that you posted this followup. It's good to know that multiple conditions, one sargable and one not sargable will still improve performance in an Access database. Thanks for testing and confirming this.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[tedsmith]

I build the criteria string before the query and using parameters that make sure the dates are valid.
That way the CVDate is only executed when assembling the Criteria String.
I can pre-trap the error if an invalid date is entered.

Criteria = ..........WHERE SomedateField > #" & CVDate(MinimumDate) & "# AND SomedateField < #" & CVDate(MaximumDate) & "#"

The database had about 50000 records and took less than I could measure (<1ms) on my i7 - 8 core.

By "between" do you mean in between or do you also want to include first and last values equal as well?
If so you can use >=

 

[CP60]

Hi tedsmith,

I want to search for a record or records with a specific date and time.
This will not always work when building a query simply using "= SomeDateTime", due to precision and the some data getting rounded (dates being stored with double precision).

You will not necessarily find all or any records, depending on the amount of precision the date is saved in the field, and the precision of the intepeted value passed.

It would be like searching for the decimal value of 1/3 stored in a field of type float, but passing the value 1/3 as type single - it will not return any records, where as using a stored and criteria value of 1/4 would.

I want the records returned to be the ones sought for and that done as fast as possible. As gmmastros pointed out, I need to do this first without the expressions, because of the mentioned reasons, until the amount of data to be initially returned is narrowed down to a minimum.

PS. Why do you still the CVDate() function? Or do you need to pass the values as variants to other routines?

 

[tedsmith]

I use the CVDate because if the user accidentally enters a date in the wrong format, it gives an error or zero. I can trap this error before executing the query and warn the user.
It also formats the query string into exactly the same format as the database field and the query seems to be quicker.

Eg if you enter just the following it reformats to
CVDate("7pm 1-21-2001") = 21/01/2001 7:00:00 PM (in Australia)

This assumes you have the database date field correctly formatted as a date (and not a string) and indexed which you should have anyway.

How is it you get "some data getting rounded"?

 

[CP60]

>Eg if you enter just the following it reformats to
>CVDate("7pm 1-21-2001") = 21/01/2001 7:00:00 PM (in Australia)

Yes, but CDate() does this too. I thought CVDate() is just the there to be compatable with code coming from older versions of vb basic.

>How is it you get "some data getting rounded"?
Well, you may eventually stumble upon this with some stored time values

I have, for example, one db table with just 10,000+ records and one with 57,000+ records.
Both have a DateTime field, updated the same way via Vb code.

When I run the following code, the results on the first table show:
Records not found with method 1= 4594
Records not found with method 1 but found with method 2= 4594

With the second table, all records were found by the first method!

This was ran on several structually identical databases, with many tables and records, and all do not have these result/problems

Public Sub test(oConn As DAO.Database)
    
    Const CTABLE As String = "Table1"
    Const CFIELD As String = "MyDate"

    Dim lCntNotFound As Long
    Dim lCntFoundWithEXPR As Long
    Dim dtValue As Date
    Dim rs As DAO.Recordset
    
    Set rs = oConn.OpenRecordset("SELECT " & CFIELD & " FROM " & CTABLE, dbOpenSnapshot)
    
    Do Until rs.EOF
        dtValue = rs.Fields(0).Value

        Call oConn.Execute("UPDATE " & CTABLE & " SET " & CFIELD & "=" & CFIELD & _
                    " WHERE " & CFIELD & "=#" & Format$(dtValue, "yyyy-mm-dd hh:nn:ss") & "#")
                    
        If oConn.RecordsAffected = 0 Then
            lCntNotFound = lCntNotFound + 1
            
            Call oConn.Execute("UPDATE " & CTABLE & " SET " & CFIELD & "=" & CFIELD & _
                        " WHERE DATEVALUE(" & CFIELD & ")=DATEVALUE(#" & Format$(dtValue, "yyyy-mm-dd hh:nn:ss") & "#)" & _
                        " AND   TIMEVALUE(" & CFIELD & ")=TIMEVALUE(#" & Format$(dtValue, "yyyy-mm-dd hh:nn:ss") & "#)")
                        
            If oConn.RecordsAffected <> 0 Then
            
                lCntFoundWithEXPR = lCntFoundWithEXPR + 1
            End If
        End If
        
        rs.MoveNext
    Loop
    rs.Close
    
    Debug.Print CStr(lCntNotFound) & ":" & CStr(lCntFoundWithEXPR)
End Sub

 

[strongm]

> I thought CVDate() is just the there to be compatable with code coming from older versions of vb basic.


So the documentation would have you think. However

msgbox cvdate(null)
msgbox cdate(null)

give entirely different results ...

 

[CP60]

But I also think it is as the doc says, for the most part.

True, in code, you are right.
?CVDate(rs.Fields("MyDateFieldIsNull").Value

But, of what value? You probably still need to next check for a null, or better use IsDate(), just in case something else gets stored in the variant.


>tedsmith the user accidentally enters
Not really needed for this though, because you can use a string and IsDate():

Under normal conditions, you are stucked with using a two or more step process: Store and Verify, or Verify and Store.

But, I don't think a MsgBox() accepts a Null

 

[tedsmith]

>because you can use a string and IsDate():
What I meant is my CVDate method creates the correctly pre-formatted Criteria String before being presented to the query rather than having to perform a computation within the query as each record is being interrogated.
I have noticed this runs faster or am I deluding myself?

The verification also automatically occurs before the query is run and if it is OK then no verification 'step' occurs within the query.

Can you explain with examples what you mean by a 'rounded' date as opposed to one that is not and how you would end up having both types in the one database that is formated as Date/Time?

I am only familiar with databases created in MSAccess so, is the exact moment the time is recorded somehow some influence in some other databases or is the precision greater than 1 second?

 

[CP60]

>What I meant is my CVDate method creates the correctly pre-formatted Criteria String before being presented to the query rather than having to perform a computation within the query as each record is being interrogated.

I think that is only if your system/user date format is set to U.S. or ISO8601, unless the db can be set to be local aware, and that local date format is identical to the sytem date format from where the query is being ran.

>I have noticed this runs faster or am I deluding myself?
No, you are right, if I understand what you mean. It will run faster because the expression is not used embedded into the sql statement, which would force the dbms to evaluate it
seperately instead of Jet using the "Microsoft Rushmore query optimisation technology", Microsoft_Jet_Database_Engine), as see by one of my previous posts here (Jet Query Plan results).

I am no expert, and am only posting things based on my experience and what I have read and understood.

>Can you explain with examples what you mean by a 'rounded' date as opposed to one that is not and how you would end up having both types in the one database that is formated as Date/Time?

Jet normally stores and returns a precision of only one second (no milliseconds).

If you let Jet format the datetime, by embedding the datetime into the statement AND use the Time expression on the value, it works. Probably because the Jet Vba Time() expression rounds the time of the db field and crioteria to the same precision. But if the time expression is passed as a string, formated as shown, or passed using CDate() (or CVDate), it might not find the row.

The problem could however be something totally unexpecting on my part, something which I have simply overlooked.

I am starting to think that the problem is probably how the values were passed which affected how they got stored, though, all values I pass are formated as #yyyy-mm-dd hh:nn:ss#.

I cannot upload a example mdb where I am at right now.

 

[CP60]

You can try it yourself by doing this (I tried it on two different machines and got the same results).

Note: I did NOT do it like this in the actual code though, it is just to demonstrate what is happening with some records):

(This is done in a MDB Module rather than in VB6 to make it quick and easy)
Create a new table called "Table1"
Add two fields to the table:
RecNr = Long
MyDate = DateTime

Public Sub test1()

    'Place cursor here (click here) and press F5

    Dim oConn As DAO.Database
    Set oConn = CurrentDb
    Call test2(oConn)
End Sub
Public Sub test2(oConn As DAO.Database)
    
    Dim dt As Date
    Dim MyValue As String
    
    MyValue = Now()
    
    oConn.Execute "INSERT INTO Table1(RecNr,MyDate) VALUES (1," & Format$(MyValue, "\#yyyy-mm-dd hh:nn:ss\#") & ")"
    
    MyValue = CDbl(Now())
    
    oConn.Execute "INSERT INTO Table1(RecNr,MyDate) VALUES (2," & Str(MyValue) & ")" '(Using STR() instead of CStr() only to force the value in my Local to U.S. format)
End Sub

The first record can be searched for and found. The second not!

You can also do a search direct in the table view of the Table with Ctrl-F using the datetime value entered, and the search will also not find the second record, though you see both values appear identical. (The second record will only be found if you use the option to either search on part on the field, or use the option to use formated values.)

Probably if I used a Querydef or Command object with a parameter, the problem would not ever have happened.