Re: assignment of data into a 2 dimensional array

Is this your query? Found it in your first post. select convert(varchar, eventime, 110) as date, cardnum from events, badge, where eventtype = 0 and devid = 1 and events.cardnum=badge.id and badge.type = 1 and convert(varchar,eventime,110)

       between '04-20-2004' and '04-21-2004'

group by events.eventime, events.cardnum order by convert(varchar,eventime,110) You'd need to do GROUP BY on the converted column, which you call date. I seem to recall that the name 'date' used to give me trouble. But anyway, assuming you called it 'date', you should be able to do: GROUP BY date, events.cardnum Alternatively: GROUP BY convert(varchar,eventime,110), events.cardnum Now if this doesn't work I'll have to fire up my MySQL box :-) /Mattias Adam Williams wrote:

Still doesn't work man, distinct isn't working properly, it still counts each swipe of the card, not discrete swipes. Thats what I'm trying to do this in PHP :) It doesn't work, its still using distinct on whatever mssql's NOW()-myssql function is before it does the date conversion, even tho that would be wrong and not following the order of operations...i guess...I dunno, all i can tell you is it doesn't work. it doesn't give the correct number when I do count(cardnum) on the dates. On Mon, 3 May 2004, Mattias Thorslund wrote:

Doesn't work, mssql doesn't have a DATE_FORMAT function. you have to use mssql's convert() function.

Oh, sorry for assuming MySQL... :-) Actually, I've done a lot more MS SQL than MySQL, anyway. On MS SQL, try: Convert(char(8), your_date, 112) as formatted_date 112 is the "style" identifier for ISO dates with 4 digist for the year: YYYYMMDD. Check out MS SQL's books online on "CAST and CONVERT" for the rest of the "style" codes if you'd like a different format. Doing this kind of stuff in the DB should be a lot cleaner and easier to maintain. Depending on your optimization, with indexes and such, it can be quicker, too. /Mattias