convert / group by date

[kirke]

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.dtDa teTime,120),
count(convert(v archar,J1708Dat a.dtDateTime,12 0))

FROM Vehicle INNER JOIN J1708Data ON Vehicle.iID = J1708Data.iVehi cleId

WHERE (J1708Data.iPid Id = 303) AND
(J1708Date.dtDa teTime between '2006-10-25' AND '2006-10-28')
AND (Vehicle.sDescr iption = $VehicleID)

GROUP BY convert(varchar ,J1708Data.dtDa teTime,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.

[MGFoster]

Re: convert / group by date

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.dtDa teTime,120),
count(convert(v archar,J1708Dat a.dtDateTime,12 0))
>
FROM Vehicle INNER JOIN J1708Data ON Vehicle.iID = J1708Data.iVehi cleId
>
WHERE (J1708Data.iPid Id = 303) AND
(J1708Date.dtDa teTime between '2006-10-25' AND '2006-10-28')
AND (Vehicle.sDescr iption = $VehicleID)
>
GROUP BY convert(varchar ,J1708Data.dtDa teTime,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.dtDateTi me,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.dtDateTi me,120)
--
MGFoster:::mgf0 0 <atearthlink <decimal-pointnet
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/AwUBRUrBwIechKq OuFEgEQKUTQCg1z GcAeAViDrJQWxEN dcn2txbhxYAoO4o
1Mks6W+FiXviMMr Zi/lt4e3z
=vWR9
-----END PGP SIGNATURE-----

[Hugo Kornelis]

Re: convert / group by date

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="SELEC T convert(varchar ,J1708Data.dtDa teTime,120),
>count(convert( varchar,J1708Da ta.dtDateTime,1 20))
>
>FROM Vehicle INNER JOIN J1708Data ON Vehicle.iID = J1708Data.iVehi cleId
>
>WHERE (J1708Data.iPid Id = 303) AND
>(J1708Date.dtD ateTime between '2006-10-25' AND '2006-10-28')
>AND (Vehicle.sDescr iption = $VehicleID)
>
>GROUP BY convert(varchar ,J1708Data.dtDa teTime,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.dtDat eTime >= '20061025'
AND J1708Date.dtDat eTime < '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.dtDa teTime,120) won't
group by daym, since the conversion doesn't chop off the time portion.
The result of select convert(varchar , current_timesta mp, 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.dtDa teTime,120),
count(convert(v archar,J1708Dat a.dtDateTime,12 0))
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