MySQL casting from decimal to string

Question

Why doesn't the CAST work in this MySQL query?

SELECT  MAX(Signups) AS Max, 
        MIN(Signups) AS Min,
        CAST(ROUND(AVG(Signups),2) AS VARCHAR(3)) AS Avg
FROM
(
    SELECT COUNT(1) AS Signups,
    DATE_FORMAT(JoinDate, "%Y-%m-%d") AS Date
    FROM users
    GROUP BY Date
) z

Why am I getting this error?

#1064 - You have an error in your SQL syntax; check the manual that corresponds 
to your MySQL server version for the right syntax to use near 
'VARCHAR(3)) AS Avg FROM ( ' at line 2

Eric Leschinski · Accepted Answer · 2013-12-07 03:51:48Z

10

This may be due to MySQL bug #34564: CAST does not accept varchar type.

Try casting to a different type, like this:

CAST(ROUND(AVG(Signups),2) AS CHAR(3)) AS Avg

edited Dec 7, 2013 at 3:51

Eric Leschinski

155k96 gold badges423 silver badges337 bronze badges

answered Feb 25, 2011 at 10:53

Lightness Races in Orbit

387k77 gold badges670 silver badges1.1k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Lightness Races in Orbit Over a year ago

Note that this was the first Google result for "mysql cast to varchar", and I found it within seconds.

super9 Over a year ago

I did not know varchar was the issue hence I did not use your search term to resolve the issue.

Lightness Races in Orbit Over a year ago

@Nai: But it's what you're doing!

super9 · Accepted Answer · 2011-02-25 08:30:23Z

1

Changing VARCHAR to CHAR solves the problem

answered Feb 25, 2011 at 8:30

super9

30.2k41 gold badges125 silver badges172 bronze badges

Collectives™ on Stack Overflow

MySQL casting from decimal to string

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related