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".
IO_DorkAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.