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" wrote in message
...
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 BNULL then the condition is Field B1 Else the condition is Field
A1


"Ken Slovak - [MVP - Outlook]" wrote in message
...
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" wrote in message
...
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]" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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 BNULL then the condition is Field B1 Else the condition is
Field A1

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" wrote in message
...
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]" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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]" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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 ?