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')

VBScript

'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

Send us your feedback about the Microsoft Exchange Server 2003 SDK.

Build: June 2007 (2007.618.1)

© 2003-2006 Microsoft Corporation. All rights reserved. Terms of use.