Posted by Hugo Kornelis on 11/03/06 22:23

On 2 Nov 2006 10:53:08 -0800, kirke wrote:

>Hi,
>I have a datetime column named dtDateTime.
>its format is "Oct 27 2006 12:00:00 "
>I want to group by only date part of it and count
>
>my code is
>
>
>$sql1="SELECT convert(varchar,J1708Data.dtDateTime,120),
>count(convert(varchar,J1708Data.dtDateTime,120))
>
>FROM Vehicle INNER JOIN J1708Data ON Vehicle.iID = J1708Data.iVehicleId
>
>WHERE (J1708Data.iPidId = 303) AND
>(J1708Date.dtDateTime between '2006-10-25' AND '2006-10-28')
>AND (Vehicle.sDescription = $VehicleID)
>
>GROUP BY convert(varchar,J1708Data.dtDateTime,120)";
>
>
>However, convert part, group by part doesnt' work at all.
>(i couldn't check count part)
>
>can you find where's the problem?
>Thx.

Hi kirke,

Have you tried to run the query? If so, what were the results? Were they
incoorrect, or did you get an error message. If the latter, then what
was that message?

I don't see any real problems with your data, thoough I would change a
few things:

* The date format. yyyy-mm-dd is not safe, becuase it can be interpreted
as yyyy-dd-mm for some country settings. Remve the dashes to get the
unambiguous yyyymmdd format.

* The use of BETWEEN means that rows with a startdate of 28th oct 2006
at exactly midnight will be included, but startdates on the same day
with a later time are excluded. The solution MGFoster proposes for this
(to include a time portion of 23:59:59) is not good enough - for
smalldatetime, this will be rounded up to the next minute, which is
midnight of the 29th of october; for datetime, you'll still miss rows
with a startdate in the last second of the day. You should replace
BETWEEN with a >= and a < condition:
AND J1708Date.dtDateTime >= '20061025'
AND J1708Date.dtDateTime < '20061029' -- Note the increased end day!
If you store all dates with the default time component of midnight, then
this is not necessary - but since it doesn't hurt either, I'd advice you
to accustom yourself to always using this techniques when comparing
datetimes.

The expression GROUP BY convert(varchar,J1708Data.dtDateTime,120) won't
group by daym, since the conversion doesn't chop off the time portion.
The result of select convert(varchar, current_timestamp, 120) for
instance is "2006-11-03 23:18:22", so you end up grouping by second.

Here's what I would try:

SELECT convert(varchar,J1708Data.dtDateTime,120),
count(convert(varchar,J1708Data.dtDateTime,120))
SELECT DATEADD(day, DATEDIFF(day, 0, d.DateTime), 0) AS TheDate,
COUNT(*) AS TheCount
FROM Vehicle AS v
INNER JOIN J1708Data AS d
ON v.VehicleID = d.VehicleId
WHERE d.PidId = 303
AND d.DateTime >= '20061025'
AND d.DateTime < '20061029'
AND v.Description = $VehicleID
GROUP BY DATEDIFF(day, 0, d.DateTime);

--
Hugo Kornelis, SQL Server MVP

[Back to original message]