0

I have created a MYSQL stored procedure that searches all columns of a table (parameter 1) to find if any of them contains a string (parameter 2). The problem is that when I compare a datetime column with a string that contains greek characters I get the following error:

SELECT * FROM my_table WHERE my_datetime LIKE '%ΕΛΛΗΝΙΚΑ%';
Illegal mix of collations for operation 'like'

The execution of the query is successful when I use latin characters. I know how to avoid checking columns based on their datatype, however I really want to be able to search for datetime strings as well. I use MySQL 5.5.24. The collation of the database is utf8_general_ci and the collation of the server is latin1_swedish_ci. I have tried the command SET NAMES utf8 before the query, with no luck though. Any ideas? Thanks.

user1173403
  • 87
  • 2
  • 8

1 Answers1

3

I had a problem with similar query:

SELECT * FROM some_table WHERE some_date LIKE '%2012-01%';

The result was an "Illegal mix of collations" error.

Using simply DATE_FORMAT function helped:

SELECT * FROM some_table WHERE DATE_FORMAT(some_date, '%Y-%m-%d') LIKE '%2012-01%';
Veilen
  • 46
  • 3