Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2003
    Location
    Nottingham, England
    Posts
    52

    Unanswered: JDBC driver support for MONTHNAME()

    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

  2. #2
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311
    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
    ________________________________________
    César Inacio Martins
    Jundiai / SP - Brasil
    http://www.imartins.com.br/informix - em Português
    http://www.imartins.com.br/informix - English (translated by Google).
    ________________________________________

  3. #3
    Join Date
    Dec 2003
    Location
    Nottingham, England
    Posts
    52
    Brilliant!
    Your a star.
    Thanks.

Posting Permissions

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