ROUNDING TIME BY HALF HOUR

Here is a formula that will return every half hour increments. Base your group on this formula.

formula @HalfHour:
<<snip>>
DateTimeVar DT := {YourTable.YourDateField};

If Minute(DT) < 30 then Time(Hour(DT),00,00)
else Time(Hour(DT),30,00);
<<snip>>

best of luck
karoo:)

I tried the above formula and received the error "A DATE-TIME IS REQUIRED HERE"

{EOR.START_DT} is the field that contains the date and time a machine started.

This is the way the formula looks using my data:


DateTimeVar DT :={EOR.START_DT};

If Minute(DT) < 30 then Time(Hour(DT),00,00)
else Time(Hour(DT),30,00);

I am using Crystal Reports v. 5.0  and I am trying to group by every half hour.

hmmm...

is {EOR.START_DT} a date or datetime field?

karoo:|

The field is a datetime field.  Below is the an example of the value.


2000/11/09 06:20:00.00


Thanks,

JB0331

hi again JB0331,

This is a bit weird, because this is a fairly simple formula and should work. (I do not have CR5 to test with but did work in CR7 and CR8)
>>"A DATE-TIME IS REQUIRED HERE"
Crystal is complaining because it is expecting a datetime. This could either be:
*the assignment (DateTimeVar DT :={EOR.START_DT};)
or
*it could be that crystal is expecting a datetime as the result of the formula. (the result is of type Time)

karoo:??

I did do some investigating,  I appears that the field {EOR.START_DT} is a text field...I think.  When I select that field into my report is appears as {XXXXXXX}.  But when I show 'current datetime' it appears as '9-12-31 01:23:45 AM'.

The output when the report runs with the above to fields is:

{EOR.START_DT =                   2000/11/09 06:20:00.00

Current datetime
11/17/00 7:50:57 AM

I don't know???


Thanks,

JB0331

I forgot to mention in the last comment that I can make {EOR.START_DT} a time field by the following command

DTSTtoTimeField({EOR.START_DT})

It then shows in the report as a time field:
1:23:45 AM


JB0331

Yep, well now it makes a lot of sense:)

OK so cystal complains because this line
>>DateTimeVar DT :={EOR.START_DT};
says:
declare a new variable DT of type datetime and set the value equal to {EOR.START_DT}, BUT the date is a string and not datetime.

Your last comment is close, change the formula to:
<<snip>>
TimeVar TM := DTSTtoTimeField({EOR.START_DT});

If Minute(TM) < 30 then Time(Hour(TM),00,00)
else Time(Hour(TM),30,00);
<<snip>>

best of luck!
karoo:)

I thought you had it there.  When I tried that command i received the following error:

A number, currency, boolean or string is expected here.


I knew that this would be tough, but this is crazy.

Any ideals?

Thanks for all the help,

JB0331

Do you get the error when you check the formula or when you run the report?

I think we are having problems b/c of minor differences between the CR versions. I'm sure we will sort it out.

still checking... will get back to you asap.

karoo:?

Same error message as the last formula.

Is there a way to first make the (EOR.START_DT} into a datetime field and then  do the DATETIME VAR and if then statement?

I can remeber way back using basic language to identify what X stood for?

grapping air on this one.


JB0331

>>Is there a way to first make the (EOR.START_DT} into a datetime

nope the type is the type and the DTSToDateTime converts the value of the field to DateTime from string.

I think there might be null values in your DB (empty dates), changed to formula to compensate for empty strings.

formula @HalfHour:
<<snip>>
DateTimeVar DT;
if {EOR.START_DT} <> '' then
  DT := DTSTtoDateTimeField({EOR.START_DT});

If Minute(DT) < 30 then Time(Hour(DT),00,00)
else Time(Hour(DT),30,00);
<<snip>>

karoo:)

hi JB0331,

I will check back again on Monday.

have a good weekend!
karoo:)

It didn't work:(

Same error message.

I don't think I have a problem with a null value.  

I receive this error message in Crystal reports and when I try to run the report.


Hope you have/had a good weekend.


Thanks,


JB0331

Good day JB0331,

Had a good weekend thank your very much.

>>I receive this error message in Crystal reports and when I try to run the report.
Well that at least tells us that Crystal is expecting something other that the time value we calculate in the formula.

There is a very good chance that the following formula will work because we convert the end result into a string. I am not absolutely sure that the sort order is going to be correct - want to try an recreate your error first. Try this:
formula @HalfHour:
<<snip>>
DateTimeVar DT := DTSTtoDateTimeField({EOR.START_DT});

If Minute(DT) < 30 then ToText(Time(Hour(DT),00,00))
else ToText(Time(Hour(DT),30,00));
<<snip>>

karoo:)

Same error...it seems like the error points to the 'DTSTtotimefield((EOR.START_DT});

The cursor after I check the formula is sitting right in front of the 'D'



JB0331

Greetings.
 
This question is still open today, perhaps it was overlooked or just lost in the volumes.  Please return to this question to update it with comments if more information is needed to get your solution.  If you've been helped by the participating expert(s), you may just convert their comment to the accepted answer and then grade and close.  If an answer has ever been proposed you may not have this option to accept the comment as answer, if that is the case, ask the specific expert you wish to award to post an answer.  This benefits others who then search our PAQ for just this solution, and rewards the experts who have provided information.  A win/win scenario.
 
If you wish to award multiple participants, you can do so by creating a zero point question in the Community Support topic area, include this link and tell them which experts you'd like to award what amounts.  If you'd like to delete this question, use the same process as above, but explain why you think it should be deleted.  Here is the Community Support link:   http://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
 
You can always click on your profile to see all your open questions, in the event you also have other open items to be resolved.   If your number of Questions Asked is not equal to the number of Answers Graded, choose to VIEW question history, and you'll quickly be able to navigate to your open items to close them as well.
 
I've had excellent help from experts-exchange through the years and find the real key to getting what I need is to remain active in all my questions, responding with results to suggestions until my solution is found, and recommend that highly.
 
Thank you very much for your responsiveness, it is very much appreciated.  
":0)  Asta
 
P.S.  Some of the older questions from last year are not in the proper comment date order, and Engineering has been advised.