Intervalmatch borders not found (in special cases)

[#1]

Hi all!!!

I need an intervalmatch the following way:

I have special meeting events during the day, for which data before has to be aggregated.

What I tried is an Intervalmatch,

create a field time from my datetime field and intervalmatched it with a mapping table.

Please see the attached file....

What is wrong seems obvious to me:

The time 2010-09-16 22:20:00.000 is NOT matched to meeting 01:00.

The time 2010-09-17 08:40:00.000 is NOT matched to meeting 13:00.

BUT

2010-09-17 05:30:00.000 is CORRECTLY matched to meeting 08:40

2010-09-17 13:00:00.000 is CORRECTLY matched to meeting 16:30

2010-09-17 16:30:00.000 is CORRECTLY matched to meeting 20:30

WHY?

Any help welcome!

Martin

[johnw · Accepted solution]

I think it's confused by how you're establishing D109.time. The time() function is used to ASSIGN a format to a time, not to convert a text field into a time. Even if you were converting a text field into a time, you're trying to convert 8 bytes of it, looking like '08:40:00' using a non-matching format of 'HH:mm:ss.fff'. If you use the time#() function instead, like you do for both Start and End, and if you use a matching format, then it works fine:

time#(right(left(text(D109_raw.EVENT_DATETIME),19),8),'HH:mm:ss') as time

[#3]

Thx!

Looking at it again - what a stupid question...

Anyway thank you very much for your help!

Martin