1/2 hour grouping not working

[pelajhia]

I am using the following formula to group by.
CR9, RealHour is a datetime.

if
minute({@RealHour}) > 29
then
totext(datetime(date({@RealHour}),time(hour({@RealHour}),30,00)))
else
totext(datetime(date({@RealHour}),time(hour({@RealHour}),00,00)))

Our business day spans from 5:30 am to 5:30 am the next day.

I had to use totext to get the hours to line up from lowest to highest, but the dates are not sorting properly.
What am I doing wrong?

My groups right now look like this:
7/15/2003 12:00:00 AM
7/15/2003 12:30:00 AM
7/15/2003 1:00:00 AM
7/15/2003 1:30:00 AM
7/15/2003 2:00:00 AM
7/15/2003 2:30:00 AM
7/15/2003 3:00:00 AM
7/15/2003 3:30:00 AM
7/15/2003 4:00:00 AM
7/15/2003 4:30:00 AM
7/14/2003 5:30:00 AM
7/15/2003 5:00:00 AM
7/14/2003 6:00:00 AM
7/14/2003 6:30:00 AM
7/14/2003 7:00:00 AM
7/14/2003 7:30:00 AM...

7/14/2003 11:00:00 PM
7/14/2003 11:30:00 PM

Note the "7/14/2003 5:30:00 AM" stuck amongst the rest of the 7/15s.

 

[synapsevampire]

You shouldn't need a totext, and the grouping looks bizarre for a totext.

Share what's in @realhour

Try grouping on:

if
minute({table.datetime}) > 29
then
cdatetime(year({table.datetime}),month({table.datetime}),day({table.datetime}),hour({table.datetime}),30,0)
else
cdatetime(year({table.datetime}),month({table.datetime}),day({table.datetime}),hour({table.datetime}),00,0)

-k

 

[pelajhia]

Here's what's in realhour:
dateadd('h',-5,{HA_REV_TFC_SUM_DETAILS.START_TIME})

For some reason the times recorded are off by 5 hours from real time, so I am correcting for that.

Starttime hours are 00, 15, 30 and 45.
I need to be able to group the 30's and 45's separately from the others.

I have tried the suggestion of moving around the parts of the date, but this does not seem to change how the date is 'seen' by crystal.
The output looks like this:
7/14/2003 9:30:00PM

 

[pelajhia]

your remark: "and the grouping looks bizarre"
made me think I must be doing something really dorky, and sure enough:
I have a bunch of groupings in this report; when I started working on it months ago, I was basing the groupings on a businessday field, and then I switched mid-stream to the startdate as my preferred field. I had to go to some of the upper groupings and reset to the starttime field.
This is working much better now.
Thanks for your time.