Very Computer

I'm a newcommer to SQLServer from Access and I'm having
difficulty with a crosstab query and how to handle dates,
I hope someone can offer me come advice.

I have a data table that serves as the central store of
some weather data. Its structure is essentially

(int)stationId, (smalldatetime)ReadingDateTime, (int)
UnitId, (float)DataValue

The values of the weather sensors are stored for each hour
of the day. What I need to do is summarise the data for
the entire day and a crosstab is the obvious way to do
this,

select StationId, ReadingDateTime,
  count(case UnitId when 6 then DataValue else NULL end)
as MyCount,
  sum(case UnitId when 6 then DataValue else NULL end) as
SolarRadn,
  avg(case UnitId when 7 then DataValue else NULL end) as
LeafR,
  avg(case UnitId when 8 then DataValue else NULL end) as
AirTemp,
  max(case UnitId when 9 then DataValue else NULL end) as
AirTemp_Max,
  min(case UnitId when 10 then DataValue else NULL end) as
AirTemp_Min,
  avg(case UnitId when 11 then DataValue else NULL end) as
VP,
  max(case UnitId when 12 then DataValue else NULL end) as
VP_Max,
  min(case UnitId when 13 then DataValue else NULL end) as
VP_Min,
  sum(case UnitId when 14 then DataValue else NULL end) as
Rain
into SCDailySummary
FROM Data
group by StationID, ReadingDateTime

The above code only does a summary by the hour, as you
would expect, the trouble that I am having is how to do
this so that the result is a summary for the day. I have
tried using the cast/convert on the ReadingDateTime to
express the datetime only as a date but this wasnt
successful. Can someone please offer me some advice on how
best to summarise this crosstab on the date and not the
date/hour.

Thanx in advance
Chris C

SELECT stationId, CONVERT(datetime,
CONVERT(char(8),ReadingDateTime,112)) AS ReadingDate,
<all your summary expressions and everything the same up to>
GROUP BY stationId, CONVERT(datetime,
CONVERT(char(8),ReadingDateTime,112))

Steve Kass
Drew University