[andrewhallam]

Just about every other DB vendor supports the MONTHNAME() SQL function but alas it seems it is not native for Informix.

e.g.
SELECT {fn MONTHNAME(orderdate)} AS month
FROM orders
gets:
Routine (monthname) can not be resolved.

Now the standard IBM ODBC driver (IBM INFORMIX ODBC DRIVER, 3.00.00.13223) does suppport this function but their JDBC driver (com.informix.jdbc.IfxDriver) doesn't.

Does anyone out there know a JDBC driver that does support this? or a work-around other than the below:

SELECT
case {fn MONTH(orderdate)}
when 1 then 'January'
when 2 then 'February'
when 3 then 'March'
when 4 then 'April'
when 5 then 'May'
when 6 then 'June'
when 7 then 'July'
when 8 then 'August'
when 9 then 'September'
when 10 then 'October'
when 11 then 'November'
when 12 then 'December'
end AS month
FROM purchaseorders

thanks in advance for any help on this.

Andy
ahmatexeldotcodotuk

[ceinma]

create function monthname ( p_dt datetime year to second) returning char(10);
define v_month char(10);



if month(p_dt)=1 then let v_month='January';
elif month(p_dt)=2 then let v_month='February';
elif month(p_dt)=3 then let v_month='March';
elif month(p_dt)=4 then let v_month='April';
elif month(p_dt)=5 then let v_month='May';
elif month(p_dt)=6 then let v_month='June';
elif month(p_dt)=7 then let v_month='July';
elif month(p_dt)=8 then let v_month='August';
elif month(p_dt)=9 then let v_month='September';
elif month(p_dt)=10 then let v_month='October';
elif month(p_dt)=11 then let v_month='November';
elif month(p_dt)=12 then let v_month='December';
end if;


return v_month;
end function

[andrewhallam]

Brilliant!
Your a star.
Thanks.