none
Strange behavior of urn:schemas:httpmail:datereceived RRS feed

  • Question

  • HI All,

         I am trying to search mails based on subject line and date received between specific dates. As in sql between is inclusive of start date and end date. My filter is as below

    string filter = "urn:schemas:mailheader:subject Like '%" + searchText + "%'" + "AND" + "(urn:schemas:httpmail:datereceived >= "+ "'" + startDate + "'"+ "AND urn:schemas:httpmail:datereceived <= "+"'" + endDate + "')";

    and my start date and enddate are as below

    DateTime startDate = new DateTime(tsdate.Year,tsdate.Month,tsdate.Day,00,00,00);

    DateTime endDate = new DateTime(esdate.Year,esdate.Month,esdate.Day,23,59,59);

    Suppose the start date is 09/04/2014 and end date is 10/04/2014. It has to search all the emails from the datetime "09/04/2014 12:00:00 am" to "10/04/2014 11:59:59 pm" . But the resultset is not picking up the emails say for example the emailed received to inbox at "09/04/2014 06:59:00 am" and specifically i find that emails are not picked till around 10:30 am and later picked up. If i change the date to 08/04/2014 it picks the emails which were received at 6:15 am. Can some one explain this strange behavior ?

    Thanks in advance,

    Prat

    
    
    Friday, April 11, 2014 12:07 AM

Answers

  • Hi Dmitry,

    I agree with your suspicion.

    Hi Prat,

    Please take a look at the article below:

    Chapter 11: Searching Outlook Data - Date-Time Comparisons.

    Because DASL queries always perform date-time comparisons in UTC, if you use a date literal in a comparison string, you must use its UTC value for the comparison. You can use the Row.LocalTimeToUTC helper function or Outlook date-time macros to facilitate the conversion.

    You should use the local time to UTC conversion functions built into the Outlook object model to perform conversions from local date-time values to UTC date-time values rather than DateTime structure conversion functions such as ToLocalTime and ToUniversalTime.

    Hope this helps.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, April 11, 2014 10:14 AM
    Moderator

All replies

  • What is your time zone? Does UTC conversion play a role?


    Dmitry Streblechenko (MVP)
    http://www.dimastr.com/redemption
    Redemption - what the Outlook
    Object Model should have been
    Version 5.5 is now available!

    Friday, April 11, 2014 1:08 AM
  • Hi there ,

              My time zone is (UTC +10:00) canberra, Melbourne, Sydney. I am getting this dates into my application using from text boxes which uses extended ajax toolkit:CalendarExtender.

    Thanks,

    Prat

    Friday, April 11, 2014 3:51 AM
  • So that makes sense, does it not? All date/.time properties on the MAPI level are stored n the UTC time zone, so when you specify the property name in the DASL format, Outlook does not perform the conversion to the local TZ and assumes the data you provide is UTC.


    Dmitry Streblechenko (MVP)
    http://www.dimastr.com/redemption
    Redemption - what the Outlook
    Object Model should have been
    Version 5.5 is now available!

    Friday, April 11, 2014 4:00 AM
  • Hi Dmitry,

    I agree with your suspicion.

    Hi Prat,

    Please take a look at the article below:

    Chapter 11: Searching Outlook Data - Date-Time Comparisons.

    Because DASL queries always perform date-time comparisons in UTC, if you use a date literal in a comparison string, you must use its UTC value for the comparison. You can use the Row.LocalTimeToUTC helper function or Outlook date-time macros to facilitate the conversion.

    You should use the local time to UTC conversion functions built into the Outlook object model to perform conversions from local date-time values to UTC date-time values rather than DateTime structure conversion functions such as ToLocalTime and ToUniversalTime.

    Hope this helps.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, April 11, 2014 10:14 AM
    Moderator