SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Illegal mix of collations for operation 'concat'

    Hi there, hope in your help.

    If try this query I don't have error:
    Code:
    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.
    Code:
    [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;
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    do a SHOW CREATE TABLE, please
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    do a SHOW CREATE TABLE, please
    thank you.
    Code:
    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;
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you make things too hard for yourself
    Code:
    SELECT CONCAT(DATE_FORMAT(myDate,'%d/%m/%Y') 
                 ,' ', myHour) AS DateHourEUR
      FROM myTable;
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Jul 2007
    Location
    San Sebastian, Spain
    Posts
    93
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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;

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by rcashell View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Enthusiast
    Join Date
    Jul 2007
    Location
    San Sebastian, Spain
    Posts
    93
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    The table is created with the default charset latin1.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by rcashell View Post
    The table is created with the default charset latin1.
    i can see that

    the question is why
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    you make things too hard for yourself
    Code:
    SELECT CONCAT(DATE_FORMAT(myDate,'%d/%m/%Y') 
                 ,' ', myHour) AS DateHourEUR
      FROM myTable;
    thank you, this version working.


    Code:
    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'
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •