Bug #67365 select date range not working with date_format

• Submitted: 24 Oct 2012 18:28
• Modified: 29 Nov 2012 20:00
• Reporter: paulo maciel
• Status: Not a Bug
• Category: MySQL Server: Command-line Clients
• Severity: S3 (Non-critical)
• Version: 5.5.27
• OS: Other (CENTOS)
• CPU Architecture: Any
• Tags: date range, date_format, from_unixtime, not working, STR_TO_DATE

[24 Oct 2012 18:28] paulo maciel

Description:
When i run:

Select date_format(from_unixtime(b.`data`),'%d/%m/%Y') as data

from activity b

where

date_format(from_unixtime(b.`data`),'%d/%m/%Y') >= date_format(str_to_date('01/10/2012','%d/%m/%Y'),'%d/%m/%Y') 

and 

date_format(from_unixtime(b.`data`),'%d/%m/%Y') <= date_format(str_to_date('24/10/2012','%d/%m/%Y'),'%d/%m/%Y';

result query returns old datas, like 03/09/2012 (%d/%m/%Y)

But when i just use

Select date_format(from_unixtime(b.`data`),'%d/%m/%Y') as data

from activity b

where

from_unixtime(b.`data`) >= str_to_date('01/10/2012','%d/%m/%Y') 
and from_unixtime(b.`data`) <= str_to_date('24/10/2012','%d/%m/%Y')

works ok.

How to repeat:
run the first script on description

[29 Nov 2012 20:00] Sveta Smirnova

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

When you compare date_format you compare strings, not numbers. And for strings sort order matters, not only digits they store. See http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html#function_strcmp for details.