Create search folder filter that converts UTC time to local?

[Mark B]

C#, VSTO, 2007

Our Add-in programmatically creates a Search folder that filters on a
user-defined field called "OurMileStone1DateTime":

today("http://schemas.microsoft.com/mapi/string/{00020329-0000-0000-C000-000000000046}/OurMileStone1DateTime")%)

However OurMileStone1DateTime is a UTC Date/Time.

I am trying to figure out how I can edit the SQL above to convert
OurMileStone1DateTime to the user's local Date/Time so the filter will then
compare that to the user's today date/time.

Either that or maybe better, try for TodayUTC. In fact as I am writing this
post I think that may be better since it would involve less calculation.

Any thoughts on how to do this using the specified syntax (which I haven't
yet been able to find a comprehensive reference document for)?

 

[Ken Slovak - [MVP - Outlook]]

All Outlook date/time properties are stored internally in UTC and
compensated to local time when retrieved using the Outlook object model. In
this case it would be easier to use a conversion of the time you want to
test to UTC for the comparison.

What syntax are you looking for a reference for?

 

[Mark B]

I looking for a reference on what "SQL" functions I can use for the filter.
Namely , I need to use an IF statement (or CASE statement -- not sure which)
to inspect whether a field is null or not. If it is then I need to perform a
greater than (>) condition on an alternate field rather than the field in
question:

If Field B<>NULL then the condition is Field B>1 Else the condition is Field
A>1

 

[Mark B]

I'm not sure I understand your sentence "In this case it would be easier to
use a conversion of the time you want to test to UTC for the comparison."

The user-defined field we have "OurMileStone1DateTime" is imported from a
text file as is. So say it is "4:00 PM, Jan 22, 2010", that is 4:00 PM Jan
22, 2010 UTC.

Are you saying that if we Outlook's today(" function as seen below, and UTC
on the user's computer is Jan 22, then Outlook will be calculate to be True
even though the user's time in the bottom right on their computer screen may
say Jan 23 (e.g. if they are in New Zealand (GMT+13))?

 

[Ken Slovak - [MVP - Outlook]]

The SQL you can use is limited and far from the complete set of SQL
functions. There really isn't a reference that I'm aware of.

What most of us do is to use the Customize View dialog and the Filter option
to create a filter using the Advanced tab. The SQL tab then shows the
resulting SQL for the filter. What you can do using the Advanced tab is
pretty much what you can do using code.

 

[Ken Slovak - [MVP - Outlook]]

If your text field is already in UTC time and is being imported using the
Outlook object model or Outlook UI then Outlook is taking that as local time
and converting it again, applying the conversion factor a second time. That
will produce incorrect information. You can verify if that is happening
using a MAPI viewer to view the time of that property. The MAPI viewer will
show you exactly how the time is being stored, in UTC.

 

[Mark B]

I saw in the MAPI viewer that it was indeed converting our UTC as though is
was local and storing it as such in UTC. So a conversion was happening.

So somehow in the SQL syntax I need to convert our MileStone1 time to local
time so Outlook's Today() function will work.

I wonder if anything in that Outlook SQL syntax would allow for this:

Today(DATEADD(minute,DATEDIFF(minute,GETUTCDATE(),GETDATE()),MileStone1))

 

[Ken Slovak - [MVP - Outlook]]

You're using managed code and you have a date/time value so you can use the
built-in managed code functions to convert to local time from UTC. Do that
and use that converted value to get the correct time entered.

 

[Mark B]

Are you saying I could call managed code functions from within that SQL
syntax or would I need to create an additional user property to store
MileStone1InLocalDateTime ?

 

[Ken Slovak - [MVP - Outlook]]

I'm saying that what you are storing is not a correct time value if it was
in UTC and is then being converted into UTC when it's stored in Outlook. You
need to provide the data to Outlook in local time. How you do that is up to
you. From there if the data is stored correctly you no longer need to do any
time conversions.