Calendar Query

[nidifice]

I have event startdate, enddate, and duration. I need to grab all of "Today's Events." Obviously getting events that start or end on today are easy enough, but I'm having trouble grabbing events that start before today and end after today. (If an event spans many days and today is in the middle.)

Any tips on a query would be great.

 

[Gixonita]

How about selecting events:

where start date <= today and
end date >= today

 

[travisbrown]

Or

GETDATE() BETWEEN startdate AND enddate

 

[nidifice]

Thanks guys.

This query is on a shared calendar stored on our exchange server. For some reason it isn't letting me use the "<" and rather than trying to figure out why I just used "NOT" logic; so, that along with an odd time problem I was getting wierd results.

Here is my query which seems to be working:
[small]
strQuery = "<?xml version=""1.0""?>" & _
"<g:searchrequest xmlns:g=""DAV:"">" & _
"<g:sql>SELECT ""urn:schemas:calendar:location"" " & _
",""urn:schemas:httpmail:subject"" " & _
",""urn:schemas:calendar:alldayevent"" " & _
",""urn:schemas:calendar:busystatus"" " & _
",""

http://schemas.microsoft.com/exchange/permanenturl""

" & _
",""DAV:id"" " & _
",""

http://schemas.microsoft.com/exchange/mid""

" & _
",""urn:schemas:calendar:uid"" " & _
",""

http://schemas.microsoft.com/exchange/outlookmessageclass""

" & _
",""DAV:creationdate"" " & _
",""urn:schemas:calendar:lastmodified"" " & _
",""urn:schemas:calendar:dtstart"" " & _
",""urn:schemas:calendar:duration"" " & _
",""DAV:getcontentlength"" " & _
",""urn:schemas:httpmail:normalizedsubject"" " & _
",""DAV:isreadonly"" " & _
",""urn:schemas:calendar:instancetype"" " & _
",""

http://schemas.microsoft.com/exchange/sensitivity""

" & _
",""urn:schemas:calendar:dtend"" " & _
",""urn:schemas:httpmail:hasattachment"" " & _
",""urn:schemas:httpmailriority"" " & _
",""

http://schemas.microsoft.com/exchange/keywords-utf8""

" & _
",""urn:schemas:httpmail:textdescription"" " & _
",""urn:schemas:httpmail:htmldescription"" " & _
"FROM Scope('SHALLOW TRAVERSAL OF """ & strCalendarURI & """') " & _
"WHERE NOT ""urn:schemas:calendar:instancetype"" = 1 " & _
"AND ""DAV:contentclass"" = 'urn:content-classes:appointment' " & _
"AND NOT ""urn:schemas:calendar:dtstart"" > '" & Today.ToString("yyyy/MM/dd") & " 23:59:59' " & _
"AND ""urn:schemas:calendar:dtend"" > '" & Today.ToString("yyyy/MM/dd") & " 05:00:00'[/color red] " & _
"ORDER BY ""urn:schemas:calendar:dtstart"" ASC" & _
"</g:sql></g:searchrequest>"[/small]

I'm not sure why I have to use 05:00:00 on my end datetime, but anything before that grabs the "all day" events on the prior day.

Exchange seems to be very picky on syntax, leading zeros and what not.

 

[vongrunt]

Gotta love XML...

Have you tried encoding < to &lt; ? Or maybe g:sql can be CDATA?

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]