MySQL Bugs: #19856: Problem in date

Bug #19856	Problem in date_format of mysql
Submitted:	16 May 2006 16:51	Modified:	17 May 2006 12:10
Reporter:	Raja Sekhar Juluri	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.21-BK, 5.0.15-Standard	OS:	Linux (Fedora Core 3)
Assigned to:	Hartmut Holzgraefe	CPU Architecture:	Any

Description:
SELECT date_format(e.CREATE_DATE,'%m/%d/%Y') FROM emp e WHERE date_format(e.CREATE_DATE,'%m/%d/%Y') <= '03/17/2006';

while running this query i am getting the results before dates and after dates.
may be this is problem in date_format in mysql

How to repeat:
SELECT date_format(e.CREATE_DATE,'%m/%d/%Y') FROM emp e WHERE date_format(e.CREATE_DATE,'%m/%d/%Y') <= '03/17/2006';

while running this query i am getting the results before dates and after dates.
may be this is problem in date_format in mysql

Thank you for a problem report. Please, specify the exact server version used, provide the CREATA TABLE statement and some data that demonstrates the behaviour you described.

I have created emp table with values

CREATE TABLE `emp` (                        
          `empNo` int(10) NOT NULL auto_increment,  
          `name` varchar(50) default NULL,          
          `create_date` datetime default NULL,      
          PRIMARY KEY  (`empNo`)                    
        ) ENGINE=MyISAM DEFAULT CHARSET=latin1      

insert into emp (name,create_date) values('emp1','2004-09-13 23:48:40'),
('emp2','2004-11-23 03:31:42'),('emp3','2004-12-01 23:54:22'),('emp4','2004-12-02 08:51:41'),
('emp5','2005-01-19 19:02:49'),('emp6','2005-01-20 23:51:58'),('emp7','2005-12-28 00:16:09'),
('emp8','2005-12-29 06:25:29'),('emp9','2006-01-03 20:55:50'),('emp10','2006-01-04 08:50:50'),
('emp11','2006-03-02 06:00:05'),('emp12','2006-04-22 04:24:47'),('emp13','2006-05-23 05:24:47'),
('emp14','2006-06-24 02:24:47')

Problem:-
while executing this query,the query returning the values before and after date

SELECT date_format(e.CREATE_DATE,'%m/%d/%Y') FROM emp e WHERE date_format(e.CREATE_DATE,'%m/%d/%YYYY') > '03/17/2005';

We're sorry, but the bug system is not the appropriate forum for 
asking help on using MySQL products. Your problem is not the result 
of a bug.

Support on using our products is available both free in our forums
at http://forums.mysql.com and for a reasonable fee direct from our
skilled support engineers at http://www.mysql.com/support/

Thank you for your interest in MySQL.

Additional info:

You are doing a string comparison here, and all your result rows for the given 
example start with "04/..." or above which is > "03/..."