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

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?

--
Ken Slovak
[MVP - Outlook]
http://www.slovaktech.com
Author: Professional Programming Outlook 2007.
Reminder Manager, Extended Reminders, Attachment Options.
http://www.slovaktech.com/products.htm


"Mark B" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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)?
>
>

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


"Ken Slovak - [MVP - Outlook]" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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?
>
> --
> Ken Slovak
> [MVP - Outlook]
> http://www.slovaktech.com
> Author: Professional Programming Outlook 2007.
> Reminder Manager, Extended Reminders, Attachment Options.
> http://www.slovaktech.com/products.htm
>
>
> "Mark B" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>> 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)?
>>
>>
>

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]" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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?
>
> --
> Ken Slovak
> [MVP - Outlook]
> http://www.slovaktech.com
> Author: Professional Programming Outlook 2007.
> Reminder Manager, Extended Reminders, Attachment Options.
> http://www.slovaktech.com/products.htm
>
>
> "Mark B" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>> 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)?
>>
>>
>

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]
http://www.slovaktech.com
Author: Professional Programming Outlook 2007.
Reminder Manager, Extended Reminders, Attachment Options.
http://www.slovaktech.com/products.htm


"Mark B" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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
>

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.

--
Ken Slovak
[MVP - Outlook]
http://www.slovaktech.com
Author: Professional Programming Outlook 2007.
Reminder Manager, Extended Reminders, Attachment Options.
http://www.slovaktech.com/products.htm


"Mark B" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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))?
>

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]" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.
>
> --
> Ken Slovak
> [MVP - Outlook]
> http://www.slovaktech.com
> Author: Professional Programming Outlook 2007.
> Reminder Manager, Extended Reminders, Attachment Options.
> http://www.slovaktech.com/products.htm
>
>
> "Mark B" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>> 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))?
>>

















>

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.

--
Ken Slovak
[MVP - Outlook]
http://www.slovaktech.com
Author: Professional Programming Outlook 2007.
Reminder Manager, Extended Reminders, Attachment Options.
http://www.slovaktech.com/products.htm


"Mark B" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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))
>

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]" <(E-Mail Removed)> wrote in message
news:e6C%(E-Mail Removed)...
> 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.
>
> --
> Ken Slovak
> [MVP - Outlook]
> http://www.slovaktech.com
> Author: Professional Programming Outlook 2007.
> Reminder Manager, Extended Reminders, Attachment Options.
> http://www.slovaktech.com/products.htm
>
>
> "Mark B" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>>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))
>>
>

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.

--
Ken Slovak
[MVP - Outlook]
http://www.slovaktech.com
Author: Professional Programming Outlook 2007.
Reminder Manager, Extended Reminders, Attachment Options.
http://www.slovaktech.com/products.htm


"Mark B" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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 ?