Logo 
Search:

Sharepoint Forum

Ask Question   UnAnswered
Home » Forum » Sharepoint       RSS Feeds

Help with SQL stuff

  Asked By: Stevie    Date: Nov 23    Category: Sharepoint    Views: 743

I have this piece of code that goes into the PUBLIC OWA Calendar and
retrieves the appointments. However the WHERE Clause doesn't seem to
be working.

I had some help on this already, but I keep messing it up when I
tinker with it.

I am getting ALL appts and not just the ones in the date range wanted.

I am looking for some help on this as soon as possible.

dtStartTime = Now
dtEndTime = DateAdd("d", intNumOfDays, Now)


' Build SQL query
strSQL = "SELECT ""urn:schemas:httpmail:normalizedsubject"", "
strSQL = strSQL & """DAV:href"", "
strSQL = strSQL & """urn:schemas:calendar:dtstart"" AS
startdate, "
strSQL = strSQL & """urn:schemas:calendar:dtend"" AS enddate "
strSQL = strSQL & "FROM scope('shallow traversal of """ &
strURL & """') "
strSQL = strSQL & "WHERE ""DAV:ishidden"" = False "
strSQL = strSQL & "AND ""DAV:isfolder"" = False "
strSQL = strSQL & "AND ""urn:schemas:calendar:dtstart"" >=
CAST(""" & Format(dtStartTime, "yyyy-mm-ddThh:mm") & """
as 'dateTime') "
strSQL = strSQL & "AND ""urn:schemas:calendar:dtend"" <= CAST
(""" & Format(dtEndTime, "yyyy-mm-ddThh:mm") & """ as 'dateTime') "
strSQL = strSQL & "ORDER BY ""urn:schemas:calendar:dtstart"""

Share: 

 

1 Answer Found

 
Answer #1    Answered By: Renata Burt     Answered On: Nov 23

Try that (tested and works here):

dtStartTime = Date
dtEndTime = DateAdd("d", intNumOfDays, Date)
strUTCStartTime = FormatUTC(dtStartTime)
strUTCEndTime = FormatUTC(dtEndTime)
strSQL = "SELECT ""DAV:href"", " & _
" ""urn:schemas:httpmail:subject"", " & _
" ""urn:schemas:calendar:dtstart"", " & _
" ""urn:schemas:calendar:dtend"", " & _
" ""urn:schemas:calendar:organizer"", " & _
" ""DAV:contentclass"" " & _
"FROM scope('shallow traversal of """ & strURL & """') " & _
"WHERE (""urn:schemas:calendar:dtend"" >= CAST(""" &
strUTCStartTime _
& """ as 'dateTime')) " & _
"AND (""urn:schemas:calendar:dtend"" <= CAST(""" & strUTCEndTime
_
& """ as 'dateTime')) " & _
"AND ""DAV:contentclass"" = 'urn:content-classes:appointment' "
& _
"ORDER BY ""urn:schemas:calendar:dtstart"" ASC"

Function FormatUTC(dtDate)

Dim strYear ' As String
Dim strMonth ' As String
Dim strDay ' As String
strYear = YEar(dtDate)
strMonth = Month(dtDate)
If Len(strMonth) = 1 Then
strMonth = "0" & strMonth
End If
strDay = Day(dtDate)
If Len(strDay) = 1 Then
strDay = "0" & strDay
End If
FormatUTC = strYear & "-" & strMonth & "-" & strDay &
"T01:00:00Z"
End Function

 
Didn't find what you were looking for? Find more on Help with SQL stuff Or get search suggestion and latest updates.




Tagged: