Solved

diff b/w CurrentTime and Timestamp field

Posted on 2014-12-04
9
233 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 3
9 Comments
 
LVL 101

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 35

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
[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

 

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 101

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 35

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 101

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 35

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

Question has a verified solution.

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

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
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…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

623 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