time diff when using time and number formats

[MartUK]

I am using cr XI

How do I get a time diff in secs when I have the recorded time as a NUMBER eg 9.45 and want to get seconds difference compared to Data Time special field which is in time format?

I want to flag up the record time against 'now' if greater than 2 hours for instance

 

[MartUK]

forgot to emphasise that the number format is shown in decimal format.
PLease can somebody help me

 

[lbass]

Please explain what time 9.45 corresponds to.
We don't know if it's hours and minutes or minutes and seconds, or whether it is 9 and 45/100 hours/minutes/seconds or 9 and 45 minutes/seconds, etc.

-LB

 

[MartUK]

9.45 would be 9 hours 45 in the morning
12.40 would be 12:40 in the afternoon etc. The database I am using is very bad . some dates and times are in date format and some are not

Once I get this conversion I then need to subtract from the CurrentTime , so maybe I also need to convert Current Time to enable me to do this. maybe convert both fileds to seconds.

I will test the result of the subtraction and flag up if say greater than equivalent of 2 hours. Hope my aim makes sense to you?

 

[lbass]

You can convert your field to time datatype by using:

time({table.yourfield})

For the difference, use:

datediff("s",datetime(currentdate,time({table.yourfield})),currentdatetime)

This assumes you are comparing times on the same date.

-LB

 

[MartUK]

Mmmm this is weird. - the first part I mean. I had this myself but could not beleive the results

I get
tt time({tt})
12.00 00:00:00
11.45 10:48:00
12.40 09:36:00
9.45 10:48:00
8.40 09:36:00

This is crazy not only are figures wrong but sometimes they give diffenet result for samedecimal figure. Very confused

 

[lbass]

Sorry, I was thinking your field was a string. Try:

time(totext({table.tt}))

-LB

 

[MartUK]

thanks this works fine and I have amended the datediff formula also.