[thelist] Re: MYSQL: getting distinct dates from a DATETIME field

[Hassan Schroeder]

Dunstan Orchard wrote:

> SELECT DISTINCT
> DATE_FORMAT(event_date, '%b %D') AS event_dates,
> event_name, event_cost
> FROM event
> WHERE (event_type = 'exhibit hall')
> ORDER BY DATE_FORMAT(event_date, '%b %D') ASC
> 
> But the output hasn't changed - it's still returning all three records :o/

What version of MySQL are you using? I just used the create statement
below to load a table on my system, ran your query "as is" -- and got
back the expected two records.

Not using PHP (jEdit + Connector/J, MySQL 4.0.14 on W2K) but I'd say
that shows the logic of the query is correct...

> Here's a dump from PhpMyAdmin showing the table structure and the three rows in
> question:
> 
> CREATE TABLE event (
>   event_id int(11) NOT NULL default '0',
>   event_name varchar(100) NOT NULL default '',
>   event_path varchar(50) NOT NULL default '',
>   event_date datetime default NULL,
>   event_duration time NOT NULL default '00:00:00',
>   event_cost int(5) NOT NULL default '0',
>   event_part int(1) NOT NULL default '0',
>   event_parts int(1) NOT NULL default '0',
>   event_note varchar(100) NOT NULL default '',
>   event_type varchar(20) NOT NULL default '',
>   PRIMARY KEY  (event_id)
> ) TYPE=MyISAM;
> 
> INSERT INTO event VALUES (13, 'Exhibit Hall Open', '', '2003-05-15 16:00:00',
> '04:00:00', 2500, 0, 0, '(Reception 4 – 6 PM)', 'exhibit hall');
> INSERT INTO event VALUES (15, 'Exhibit Hall Open', '', '2003-05-16 08:00:00',
> '05:00:00', 2500, 0, 0, '', 'exhibit hall');
> INSERT INTO event VALUES (21, 'Exhibit Hall Open', '', '2003-05-16 16:00:00',
> '03:00:00', 2500, 0, 0, '', 'exhibit hall');
> 
> 
> And the data being outputted to the page:
> 
> Date 	        Cost 	  	Title
> May 15th 	$25 		Exhibit Hall Open
> May 16th 	$25 		Exhibit Hall Open
> May 16th 	$25 		Exhibit Hall Open

-- 
Hassan Schroeder ----------------------------- hassan at webtuitive.com
Webtuitive Design ===  (+1) 408-938-0567   === http://webtuitive.com

                           dream.  code.