Specifying a Date Range
Specifying a Date Range
This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.
This example filters for items based on a date range. The results of the search in this example are passed to the DoResults function in Enumerating Search Results.
Because Exchange store date values use the DBTYPE_TIMESTAMP data type, you must include an exact time specification to match a particular date. For example, the following WHERE Clause will not match items created on 2000-04-05:
WHERE "DAV:creationdate" = '2000-04-05'
To match, the date must be adjusted from local time to coordinated universal time (UTC) (because dates are stored in UTC), and then converted to Industry Standards Organization (ISO) date format (yyyy-mm-ddThh:mm:ssZ). Finally, the CAST Function must be used to cast the date to the DateTime data type, as shown in the following WHERE Clause:
WHERE "DAV:creationdate" = CAST("2000-04-05T01:00:00Z" as 'dateTime')
Note that the "dateTime" must be used with an uppercase T to work. A practical way to match a date is to define the range between the day before and the day after, as shown in this clause:
WHERE "DAV:creationdate" > CAST("2000-04-04T01:00:00Z" as 'dateTime') AND "DAV:creationdate" < CAST("2000-04-06T01:00:00Z" as 'dateTime')
'Finds items matching a date range 'Passes search result recordset to DoResults (see Enumerating Results) On Error GoTo ErrHandler Const adErrNoCurrentRecord = 3021 Dim info Dim infoNT Dim Conn Dim cName Dim dName Dim cURL Dim relURL Dim DateRng Dim strQ Dim Rs 'get computer and domain information Set info = CreateObject("ADSystemInfo") Set infoNT = CreateObject("WinNTSystemInfo") cName = infoNT.ComputerName dName = info.DomainDNSName 'create connection object Set Conn = CreateObject("ADODB.Connection") Conn.Provider = "Exoledb.DataSource" 'URL for connection object 'is at the virtual directory root cURL = "http://" & cName & "." & dName & "/" & "public" Conn.Open cURL 'relative URL is the folder to search relURL = "Reports" 'date range variable: DateRng = " > CAST(""2000-06-12T01:00:00Z"" as 'dateTime')" Set Rs = CreateObject("ADODB.Recordset") 'construct the SQL query strQ = "SELECT ""urn:httpmail:subject"" " strQ = strQ & "FROM """ & relURL & """ " strQ = strQ & "WHERE ""urn:schemas:httpmail:datereceived"" " & DateRng Rs.Open strQ, Conn 'If empty recordset, return error 'If successful call DoResults routine passing the recorset If Rs.EOF = True Then Response.Write "No items found, run another query." Else Response.Write "Success! Found " & Rs.RecordCount DoResults Rs End If GoTo Ending ' Implement custom error handling here. ErrHandler: WScript.echo Err.Number + " " + Err.Description Err.Clear Ending: Conn.Close Rs.Close Set Conn = Nothing Set Rs = Nothing