Illegal mix of collations for operation 'concat'

[cms9651]

Hi there, hope in your help.

If try this query I don't have error:

SELECT
	CONCAT(REPLACE (
		DATE_FORMAT(
			myDate,
			GET_FORMAT(DATE, 'EUR')
		),
		'.',
		'/'
	), ' ', '11:22') AS `DateHourEUR`
FROM myTable;

If try this query I have error, why?
thank you.

[Err] 1270 - Illegal mix of collations (utf8_general_ci,COERCIBLE), (utf8_general_ci,COERCIBLE), (latin1_swedish_ci,IMPLICIT) for operation 'concat'

SELECT
	CONCAT(REPLACE (
		DATE_FORMAT(
			myDate,
			GET_FORMAT(DATE, 'EUR')
		),
		'.',
		'/'
	), ' ', myHour) AS `DateHourEUR`
FROM myTable;

[r937]

do a SHOW CREATE TABLE, please

[cms9651]

do a SHOW CREATE TABLE, please

thank you.

DROP TABLE IF EXISTS `myTable`;
CREATE TABLE `myTable` (
  `myDate` date DEFAULT NULL,
  `myHour` varchar(255) DEFAULT NULL,
  `ID` int(10) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`ID`),

) ENGINE=InnoDB AUTO_INCREMENT=16384 DEFAULT CHARSET=latin1;

[r937]

you make things too hard for yourself

SELECT CONCAT(DATE_FORMAT(myDate,'%d/%m/%Y') 
             ,' ', myHour) AS DateHourEUR
  FROM myTable;

[rcashell]

To respond to the issue you are having this is down to you connecting with UTF8 as the connection characterset. It is then attempting to CONCAT UTF8 strings with latin1. Either convert the UTF8 to latin1 or vice versa. For example:

SELECT
CONCAT(REPLACE (
DATE_FORMAT(
myDate,
GET_FORMAT(DATE, 'EUR')
),
'.',
'/'
), ' ', myHour COLLATE utf8_general_ci) AS `DateHourEUR`
FROM myTable;

[r937]

Either convert the UTF8 to latin1 or vice versa.

... i'd be interested in hearing how my solution worked out (above)

it's also interesting that there are no text columns in the given table, so the latin1 is unnecessary

the fact that the "myTime" column is VARCHAR(255), but holds values like '11:22', is yet another WTF

[rcashell]

The table is created with the default charset latin1.

[r937]

The table is created with the default charset latin1.

i can see that

the question is why

[cms9651]

you make things too hard for yourself

SELECT CONCAT(DATE_FORMAT(myDate,'%d/%m/%Y') 
             ,' ', myHour) AS DateHourEUR
  FROM myTable;

thank you, this version working.

SELECT
CONCAT(REPLACE (
DATE_FORMAT(
myDate,
GET_FORMAT(DATE, 'EUR')
),
'.',
'/'
), ' ', myHour COLLATE utf8_general_ci) AS `DateHourEUR`
FROM myTable;

With this version I have error:
[Err] 1253 - COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'latin1'