Posted by MGFoster on 11/03/06 04:12

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?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I'd have done it like this (use VARCHAR(10) or CHAR(10) for the date
instead of an unspecified size):

SELECT CONVERT(VARCHAR(10),J.dtDateTime,120) As theDate,
COUNT(*) As theCount
FROM Vehicle As V INNER JOIN J1708Data As J
ON V.iID = J.iVehicleId
WHERE J.iPidId = 303
AND J.dtDateTime BETWEEN '2006-10-25' AND '2006-10-28 23:23:59'
AND V.sDescription = @VehicleID
GROUP BY CONVERT(VARCHAR(10),J.dtDateTime,120)
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRUrBwIechKqOuFEgEQKUTQCg1zGcAeAViDrJQWxENdcn2txbhxYAoO4o
1Mks6W+FiXviMMrZi/lt4e3z
=vWR9
-----END PGP SIGNATURE-----

[Back to original message]