Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Calculate intervals

Posted on 2011-09-30
10
Medium Priority
?
248 Views
Last Modified: 2012-05-12
Whats the correct way to write a formula to calculate the difference between

(Occ_date & occ_time ) - charted_date & charted_time when the data appears as below?

OCC_DATE      OCC_TIME      CHARTED_DATE      CHARTED_TIME
20110808      14:00      20110808      15:41
20110808      14:00      20110808      15:41
20110808      14:00      20110808      15:41
20110808      16:00      20110808      19:18
20110808      21:00      20110808      20:13
0
Comment
Question by:hwassinger
[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
  • 4
  • 4
  • 2
10 Comments
 
LVL 101

Expert Comment

by:mlmcc
ID: 36894601
Do you have 4 fields or 2 fields

Are they Strings or Date and Time fields?

Crystal has a DateDiff function.  You can get the difference in many different intervals (seconds, minutes, hours, days, etc)

DateDiff("s",{StartDateTimeField}, {EndDateTimeField})

In your case if the fields are strings with the data shown, try this formula

 
Local StringVar strStartDate := {Occ_date};
Local StringVar strStartTime := {Occ_Time};
Local StringVar strEndDate := {charted_date};
Local StringVar strEndTime := {charted_time};

Local DateTimeVar StartDateTime;
Local DateTimeVar EndDateTime; 
StartDateTime := DateTime(Date(Picture(strStartDate,'xxxx/xx/xx')),Time(strStartTime));
EndDateTime := DateTime(Date(Picture(strEndDate,'xxxx/xx/xx')),Time(strEndTime));

DateDiff('s',StartDateTime,EndDateTime)

Open in new window


mlmcc
0
 

Author Comment

by:hwassinger
ID: 36895350
Thanks, I'll validate tis in the AM.
0
 
LVL 35

Expert Comment

by:James0628
ID: 36896085
First of all, for the record, we are assuming that your date field is yyyymmdd.  Since your examples are all 0808, it's impossible to be sure whether that's mmdd or ddmm, so I wanted to mention it.

 FWIW, assuming that you have 4 string fields, here are a couple of alternatives.  They both do the same thing as mlmcc's formula.  Just in a slightly different way, and are a bit shorter.

DateDiff ("s", DateTime (Picture ({OCC_DATE}, "xxxx/xx/xx") + " " + {OCC_TIME}),
 DateTime (Picture ({CHARTED_DATE}, "xxxx/xx/xx") + " " + {CHARTED_TIME}))


 Or, breaking the date fields down instead of using Picture:

DateDiff ("s",
 DateTime (Left ({OCC_DATE}, 4) + "/" + Mid ({OCC_DATE}, 5, 2) + "/" +
  Right ({OCC_DATE}, 2) + " " + {OCC_TIME}),
 DateTime (Left ({CHARTED_DATE}, 4) + "/" + Mid ({CHARTED_DATE}, 5, 2) + "/" +
  Right ({CHARTED_DATE}, 2) + " " + {CHARTED_TIME}))


 I like the Picture one myself.  Much simpler.  But thought I'd post the longer version too, in case you wanted to be able to see how the string date was being broken down and the "/"s added.  FWIW, if I was trying to use this in a record selection formula, like to only select the records with more than X days between those dates, then I'd probably use the last formula.  It seems most likely to be passed to the server.  In particular, I don't know if CR would be able, or try, to translate the Picture function into something that the server could understand.  Maybe it would.  Left/Mid/Right just seems "safer".

 James
0
Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

 

Author Comment

by:hwassinger
ID: 36896730
Mimcc This returns an error"Bad time format string.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 36896784
Is the field a string?

It worked in CR XI

Can any of the fields be NULL?

mlmcc
0
 

Author Comment

by:hwassinger
ID: 36896848
The field is stored as a string and there should be no null fields
0
 
LVL 101

Accepted Solution

by:
mlmcc earned 2000 total points
ID: 36896890
Could some have a bad time like 24:30 or 15:65

Might try this


 
Local StringVar strStartDate := {Occ_date};
Local StringVar strStartTime := {Occ_Time};
Local StringVar strEndDate := {charted_date};
Local StringVar strEndTime := {charted_time};

Local DateTimeVar StartDateTime;
Local DateTimeVar EndDateTime; 
If IsTime(strStartTime) then
    StartDateTime := DateTime(Date(Picture(strStartDate,'xxxx/xx/xx')),Time(strStartTime))
Else
    StartDateTime := DateTime(Date(Picture(strStartDate,'xxxx/xx/xx')),Time(0,0,0));

If IsTime(strEndTime) then
    EndDateTime := DateTime(Date(Picture(strEndDate,'xxxx/xx/xx')),Time(strEndTime))
Else
    EndDateTime := DateTime(Date(Picture(strEndDate,'xxxx/xx/xx')),Time(0,0,0));

DateDiff('s',StartDateTime,EndDateTime)

Open in new window


You can also test dates the same way with IsDate

mlmcc
0
 

Author Comment

by:hwassinger
ID: 36896930
Mimcc is this returning in seconds? If so how to convert to minutes but this looks correct
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 36897490
Yes, it is returning seconds.
Interval is the first argument.  Use n for minutes

      DateDiff('n',StartDateTime,EndDateTime)

mlmcc
0
 
LVL 35

Expert Comment

by:James0628
ID: 36900681
Or, if you want a little more precision, get the number in seconds and then divide by 60 to convert it to minutes.

 As for the error that you were getting, if you have some bad date or time strings, you should try to figure out what they are and why they're there.  If users are entering them, maybe there are issues there that you need to look at.  For example, maybe they're just sometimes entering the dates as yyyyddmm, instead of yyyymmdd.

 James
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

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…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Suggested Courses

618 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