Specifying a Date Range

Topic Last Modified: 2006-12-05

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