MySql problem

[ChrisM]

I have been having trouble converting numeric values to month names.

I did try to use the monthname(date) function for viewing on the x-axis of a chart and it returns garbled info there.

If I use month(date) in the query, it returns numbers just fine

I need these numbers converted into 3 character abbreviated month names.

It seems like it should be an easy thing to do, but for whatever reason, I can't find any information anywhere about why the monthname(date) function is messing up.

Here is the code I'm using...

SELECT month(closeddate) AS monthclosed,
avg(salepricedisplay) as avgsaleprice
FROM currentmerge
where liststatus='sold'
group by monthclosed

this works perfect, but just needs to have the month name instead of the month number. The chart still works when going to

select monthname(closeddate) as monthclosed

but it returns garbled response instead of a real month name.

Anyone know whats up here? or what to do?

Thanks in advance for any help!

[SQL Maestro]

I check this function in my MySQL server ver 4.1

CREATE TABLE `tes1t` (
`id` int(11) NOT NULL auto_increment,
`closeddate` date default NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

select MONTHNAME(closeddate) from tes1t;

result: December

[ChrisM]

The problem actually isn't with the sql query it seems.

It is with cfchart in coldfusion storing the month name in a binary format and not reprocessing it when needing to be displayed on the chart.

Any ideas how to work through this?

I know this is more for SQL stuff, so if not thanks anyhow.

I appreciate the reply

[SQL Maestro]

SELECT CASE month(closeddate) WHEN 1 THEN "Jan" WHEN 2 THEN "Feb" WHEN 3 THEN "Mar" WHEN 4 THEN "Apr" WHEN 5 THEN "May" WHEN 6 THEN "Jun" WHEN 7 THEN "Jul" WHEN 8 THEN "Aug" WHEN 9 THEN "Sep" WHEN 10 THEN "Oct" WHEN 11 THEN "Nov" WHEN 12 THEN "Dec" ELSE "false" END AS monthclosed FROM users;