Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 239
  • Last Modified:

diff b/w CurrentTime and Timestamp field

How do I write a formula field that calculates the difference between the CurrentTime and my database field {proposal.timestamp}?

I tried datediff function, but that did not work, it did not like me using the crystal function "CurrentTime".
0
IO_Dork
Asked:
IO_Dork
  • 3
  • 3
  • 3
4 Solutions
 
mlmccCommented:
WHat data is in TimeStamp?

What type is it?

I suspect it may be an integer showing milliseconds rather than a time.

It may also be a datetime type with the date being 1 Jan 1900.

Normally you would use

DateDiff('s',{proposal.timestamp},CurrentTime())

You might try

DateDiff('s',{proposal.timestamp},CurrentDateTime())

mlmcc
0
 
James0628Commented:
CurrentTime won't work, because that is _only_ the time.  DateDiff requires date or datetime arguments (which would explain why it's called DateDiff and not TimeDiff ;-).  Maybe you thought/assumed that CurrentTime included the date.

 If you want the current date and time, use CurrentDateTime.  Assuming that your field is a datetime, it'd be something like mlmcc's last suggestion, although you don't need the () after CurrentDateTime.

 If you actually want to compare just the times and ignore the dates, the simplest thing might be to combine each time with the current date, and then use DateDiff on those datetimes.

 James
0
 
IO_DorkAuthor Commented:
my timestamp includes date and time, so I should be able to use datediff with currentdatetime.  I'll try that.
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
IO_DorkAuthor Commented:
ok, that worked, but how to I get the datediff result to show as hours:minutes instead of just a number that represents total minutes?

for example, it is showing a record with a diff of 132 minutes, I want it to display as 2:12.
0
 
mlmccCommented:
You will have to convert it yourself.

Try this formula

Local NumberVar MinuteDiff;
Local NumberVar HoursDiff;

MinuteDiff := DateDiff("m",{proposal.timestamp},CurrentDateTime);
HoursDiff := MinutesDiff \ 60;
MinutesDiff := MinutesDiff MOD 60;

CStr(HoursDiff,0,'') & ':' & CStr(MinutesDiff,'00')

Open in new window


mlmcc
0
 
James0628Commented:
I assume that the difference will never be negative.

 Will the difference ever be more than 24 hours?  If so, how do you want to display those values?

 If the difference will always be less than 24 hours, you could use something like this:

Time ((DateDiff ('n', {proposal.timestamp}, CurrentDateTime) / 60) / 24)

 The Time function can be given a value that's expressed in 24 hour units, so that formula divides the minutes by 60 to convert it to hours, and then divides that by 24 to get "24 hour units", which are then converted into a time.  You could also just divide the minutes by 1440 (24 * 60).  You can format the time however you like (probably in a 24-hour, or "military time", format).  But if the value is more than 24 hours, the time just wraps around (eg. 25 hours will be 1 AM), so you'd have to decide how you wanted to display those values.

 James

 PS: mlmcc hadn't posted that formula when I started working on my post.  Of course you could use a formula like that.  I just decided to try to come up with something "simpler" (it's only one line), although it does have its limits.  But the interval code in mlmcc's formula should be "n", not "m" (which is months).
0
 
mlmccCommented:
Thanks James.  Too much in other reporting tools where m is minutes and M is months

mlmcc
0
 
James0628Commented:
No problem.  It's easy to forget.

 James
0
 
IO_DorkAuthor Commented:
Thanks. I'll try this out when I am back at work Monday.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now