Can i index date_format(columnname,'%Y-%m-%d) in MySQL ?

Question

Dear mansur, you don't answer my question.

Answer 1

Hi,
Remember date_format formats the date value according to the format string. Therefore:

mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y');
-> 'Saturday October 1997'
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%H:%i:%s');
-> '22:23:00'
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',
'%D %y %a %d %m %b %j');
-> '4th 97 Sat 04 10 Oct 277'
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',
'%H %k %I %r %T %S %w');
-> '22 22 10 10:23:00 PM 22:23:00 00 6'
mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V');
-> '1998 52'
mysql> SELECT DATE_FORMAT('2006-06-00', '%d');
-> '00'


Are some examples....
So tecnically your syntax correct...
But now back to your question, when you mean index, that means your placing a index key within that field for that scheme.

By indexing a time field is not appropriate. MySQL has its own alogotithmic ideology based on the date and time fields.

But if your always placing that forma, you could always use as your datatype DATETIME or just DATE.

And if you wish to browse the dates use the BETWEEN clause.

Enjoy!