BorlandTalk.com

Larry · Guest

In the quesry below, I am aggregating my data by hourly groups. Works great
but I want to show my user time as 10:00 rather then 10. I can handle this
in reports since I can just 'glue' the :00 to the hour but I can't figure
out how to do this in a TcxGrid so I firgured I just do it in the SQL
statement.

What I want to do is cast the Hour part of the Select clause to a Char(5)
and then cat a ':00' to it.

I have been screwing around with it but can't seem to get it right.
Any help would be appreciated.
Thanks,
Larry

ps I know this belongs in a MS SQLserver.programming NG but I can't get on
that NG from here.

Declare @ProdWorkDateStart DateTime,
@ProdWorkDateEnd DateTime

Set @ProdWorkDateStart = '3/19/2007 12:00 AM' -- :ProdWorkDateStart
Set @ProdWorkDateEnd = '3/20/2007 12:00 AM' -- :ProdWorkDateEnd

Select Count(*)QTY, Avg(Weight) AvgWt, Sum(Weight) as TotWt,
(Sum(Weight)-Sum(Target)) as GiveAway,
Shift, ProdWorkDate, ScaleID,
Case DatePart(hh,DateWeighed)When 0 then 24 Else DatePart(hh,DateWeighed)end
as Hour
From dbo.DualIndexer
Where (ProdWorkDate >= @ProdWorkDateStart )
and (ProdWorkDate <= @ProdWorkDateEnd )
and Shift = 1

Group By Shift, ProdWorkDate, ScaleID,
Case DatePart(hh,DateWeighed)When 0 then 24 Else
DatePart(hh,DateWeighed)end
Order by Shift, ProdWorkDate,
Case DatePart(hh,DateWeighed)When 0 then 24 Else
DatePart(hh,DateWeighed)end

Larry · Guest

I got it figured. For anyone that cares, I used

(Cast((Case DatePart(hh,DateWeighed)When 0 then 24 Else
DatePart(hh,DateWeighed)end) as VarChar(5)) +':00') As Hour

"Larry" <lkillen (AT) charter (DOT) net> wrote in message
news:461cf3c9$1 (AT) newsgroups (DOT) borland.com...