467,161 Members | 880 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,161 developers. It's quick & easy.

convert / group by date

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.

Nov 2 '06 #1
  • viewed: 11599
Share:
2 Replies
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 <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/AwUBRUrBwIechKqOuFEgEQKUTQCg1zGcAeAViDrJQWxENdcn2t xbhxYAoO4o
1Mks6W+FiXviMMrZi/lt4e3z
=vWR9
-----END PGP SIGNATURE-----
Nov 3 '06 #2
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
Nov 3 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by h_ghanaty | last post: by
1 post views Thread by Raja | last post: by
1 post views Thread by Sam | last post: by
4 posts views Thread by Daniel Kaseman | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.