Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
Thx!
Looking at it again - what a stupid question...
Anyway thank you very much for your help!
Martin