DateTime Datatype Woes, differences to the right of the decimal

[#1]

Hello my fine Qlik friends!

After a few hours research, I found no answer to this question: Can I truncate a double float fractional part to a given precision?

Problem:  I have a dataset containing various datetime objects.  In one table, I have an event, a Start Time and an End Time.  It is loaded like this:

Schedule:

LOAD [Clndr Date] AS Date,

     [Agent Corp Login] AS Login,

     Code,

     Descr,

     Time(Frac([Start Moment]), 'hh:mm:ss') AS [Start Moment],

     Time(Frac([Stop Moment]), 'hh:mm:ss') AS [Stop Moment],

     Interval(Time([Stop Moment])-Time([Start Moment]), 'mm') AS Duration

FROM

[..\2.UserData\Schedule.xlsx]

(ooxml, embedded labels, table is [Schedule]);

In another table I have 30 minute time intervals defined in a spreadsheet: 

Interval Start	Interval End
0:00:00	0:29:59
0:30:00	0:59:59
1:00:00	1:29:59
1:30:00	1:59:59

I use Intervalmatch to link the Start Time to a given Interval and this works well. ...almost.  When I threw the resulting dataset into the report block to verify, I noticed NULL values:

Using some recently learned troubleshooting techniques, I created a Listbox showing both the formatted values and the Num() values and noticed this: 

This discrepancy is consistent for all non-matching rows.  The last 4 digits to the right of the decimal are different.  I've tried various attempts at Round(), I also toyed with a few other likely functions but what I think I need is some consistent way to lop off or restrict the last few digits of the Start Time and perhaps the Interval Start as well.

Any ideas are greatly appreciated.

Qlik On!

mfc

[sunny_talwar · Accepted solution]

Did you try

Round(<Expression>, 1/86400)

[#3]

Thanks for the suggestion, Sunny T but it made no difference.

[#4]

My apologies Sunny T,

it depended upon where I placed the Round function. My original script Load statement:

          Time(Frac([Start Moment]), 'hh:mm:ss') AS [Start Moment],

I then took your advice and tried:

          Time(Frac(Round([Start Moment],1/86400)), 'hh:mm:ss') AS [Start Moment],

But that made no difference.  After reading John Witherspoons advice Numbers don't add up

I realized I had made the Round() conversion in the wrong place.  I tried again:

          Time(Round(Frac([Start Moment]),1/86400), 'hh:mm:ss') AS [Start Moment],

SUCCESS!!

Thank you for both the correct answer and your patience