[thelist] Re: MYSQL: getting distinct dates from a DATETIME field
Hassan Schroeder
hassan at webtuitive.com
Sun Oct 5 09:15:20 CDT 2003
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.
More information about the thelist
mailing list