Create search folder filter that converts UTC time to local?

Status
Not open for further replies.
M

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)?
 
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" <none123@none.com> wrote in message

news:%23vVQsvWmKHA.5040@TK2MSFTNGP06.phx.gbl...
> 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
<kenslovak@mvps.org> wrote in message

news:ulf2wyemKHA.5840@TK2MSFTNGP05.phx.gbl...
> 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" <none123@none.com> wrote in message
> news:%23vVQsvWmKHA.5040@TK2MSFTNGP06.phx.gbl...
> > 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))?
<kenslovak@mvps.org> wrote in message

news:ulf2wyemKHA.5840@TK2MSFTNGP05.phx.gbl...
> 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" <none123@none.com> wrote in message
> news:%23vVQsvWmKHA.5040@TK2MSFTNGP06.phx.gbl...
> > 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.

"Mark B" <none123@none.com> wrote in message

news:edKZIyomKHA.1536@TK2MSFTNGP06.phx.gbl...
> 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.

"Mark B" <none123@none.com> wrote in message

news:%23uUwB3omKHA.5728@TK2MSFTNGP06.phx.gbl...
> 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))
<kenslovak@mvps.org> wrote in message

news:OiIEB32mKHA.1548@TK2MSFTNGP04.phx.gbl...
> 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" <none123@none.com> wrote in message
> news:%23uUwB3omKHA.5728@TK2MSFTNGP06.phx.gbl...
> > 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.

"Mark B" <none123@none.com> wrote in message

news:OjiCNPBnKHA.5508@TK2MSFTNGP02.phx.gbl...
> 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 ?
<kenslovak@mvps.org> wrote in message

news:e6C%23ffcnKHA.1544@TK2MSFTNGP06.phx.gbl...
> 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" <none123@none.com> wrote in message
> news:OjiCNPBnKHA.5508@TK2MSFTNGP02.phx.gbl...
> >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.

"Mark B" <none123@none.com> wrote in message

news:%23aO48FknKHA.5524@TK2MSFTNGP05.phx.gbl...
> 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 ?
 
Status
Not open for further replies.
Similar threads
Thread starter Title Forum Replies Date
S Create A Search Folder That Looks For Message Class? Outlook VBA and Custom Forms 0
W Create Search Folder excluding Specific Email Addresses Using Outlook 5
A Outlook macro to create search folder with mail categories as criteria Outlook VBA and Custom Forms 3
Diane Poremsky Use VBA to create an Outlook Search Folder for Sender Using Outlook 0
D Create advanced search (email) via VBA with LONG QUERY (>1024 char) Outlook VBA and Custom Forms 2
S Create Outlook Task from Template and append Body with Email Body Outlook VBA and Custom Forms 4
B Modify VBA to create a RULE to block multiple messages Outlook VBA and Custom Forms 0
J Want to create a button on the nav bar (module add-in) to run code Outlook VBA and Custom Forms 2
B How to create a button that sorts and selects the most recent message with ONE click Using Outlook 2
J PSA: How to create custom keyboard shortcut for "Paste Unformatted Text" in Outlook on Windows Outlook VBA and Custom Forms 1
W Create a Quick Step or VBA to SAVE AS PDF in G:|Data|Client File Outlook VBA and Custom Forms 1
Wotme create email only data file Using Outlook 1
S Outlook 365 Help me create a Macro to make some received emails into tasks? Outlook VBA and Custom Forms 1
J How to create a drop down user defined field that will appear on an inbox view Outlook VBA and Custom Forms 8
Commodore Any way to create "from-only" account on Outlook 2021? Using Outlook 1
L Capture email addresses and create a comma separated list Outlook VBA and Custom Forms 5
N Can't create NEW GROUP and add/remove a member from existing Group in Outlook Using Outlook 1
NVDon Create new Move To Folder list Outlook VBA and Custom Forms 0
C Create Meeting With Custom Form Outlook VBA and Custom Forms 2
G Create ordinal numbers for birthday Outlook VBA and Custom Forms 2
O Outlook 365 - How to create / copy a new contact from an existing one? Using Outlook 5
D Create new email from the received Email Body with attachment Outlook VBA and Custom Forms 10
A How to create fixed signatures for aliases that process through GMAIL? Outlook VBA and Custom Forms 0
P Can I create a Rule that sends me an email when I get a Task? Using Outlook 2
M How create a Rule to filter sender's email with more that one @ sign Using Outlook 1
B Can I create a local PST file for SPAM on a drive that is usually disconnected? Using Outlook 3
Chiba Create an appointment for all the members Outlook VBA and Custom Forms 1
S Create a clickable custom column field Outlook VBA and Custom Forms 0
O Create a custom contact form - questions before messing things up... Outlook VBA and Custom Forms 4
L automaticaly create a teams meeting with a sync Using Outlook 0
D Can Exchange Admin Center create a pst for users email/contacts/calendar? Exchange Server Administration 0
F How to create phone number as links in notes of Contacts Using Outlook 2
Nessa Can't create new appointment Using Outlook 1
A Create date folder and move messages daily Outlook VBA and Custom Forms 1
C Create new Message with shared contacts & BCC'ing recipients Outlook VBA and Custom Forms 0
O Multiple email accounts - hesitate to create a new profile Using Outlook 3
G Can't create Folder Groups in Outlook 2013 Using Outlook 0
N Outlook rules don't create a copy for bcc'ed emails Using Outlook 3
F Delete/create/reset Exchange mailbox on Outlook.com Using Outlook.com accounts in Outlook 3
R Can not create folder to store specific emails in in Outlook for Mac Using Outlook 1
K VBA BeforeItemMove event create rule to always move to its folder. Outlook VBA and Custom Forms 4
JackBlack What tools do you use to create the signature for email? Using Outlook 3
Rupert Dragwater How to create a new email with @outlook.com Using Outlook.com accounts in Outlook 32
F Should a new email account also create new contacts Using Outlook 2
D create an html table in outlook custom form 2010 using vba in MsAccess Outlook VBA and Custom Forms 7
R Outlook add-in to create new contact from an email. Using Outlook 0
Tanja Östrand Outlook 2016 - Create Macro button to add text in Subject Outlook VBA and Custom Forms 1
Q Script to create a pst file for Archiving Using Outlook 1
Jennifer Murphy Can I create a Rule with Or'd conditions? Using Outlook 1
D Outlook macros to create meeting on shared calendar Outlook VBA and Custom Forms 10

Similar threads

Back
Top