Solved

diff b/w CurrentTime and Timestamp field

Posted on 2014-12-04
9
224 Views
Last Modified: 2014-12-08
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
Comment
Question by:IO_Dork
  • 3
  • 3
  • 3
9 Comments
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 250 total points
ID: 40481945
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
 
LVL 34

Assisted Solution

by:James0628
James0628 earned 250 total points
ID: 40482576
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
 

Author Comment

by:IO_Dork
ID: 40483434
my timestamp includes date and time, so I should be able to use datediff with currentdatetime.  I'll try that.
0
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 

Author Comment

by:IO_Dork
ID: 40483495
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
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 250 total points
ID: 40483923
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
 
LVL 34

Accepted Solution

by:
James0628 earned 250 total points
ID: 40483929
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 40484277
Thanks James.  Too much in other reporting tools where m is minutes and M is months

mlmcc
0
 
LVL 34

Expert Comment

by:James0628
ID: 40484479
No problem.  It's easy to forget.

 James
0
 

Author Comment

by:IO_Dork
ID: 40484779
Thanks. I'll try this out when I am back at work Monday.
0

Featured Post

Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

778 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question