oracle-databaseodbcinformixdata-conversionyearmonth

ODBC Informix server: Error while Extracting Month name from the date


I am trying to get the Month name from the date so that I can use the month in the Tableau. Using the MONTH function, I am getting the Month number 1-12 which is coming as Measure Columns in the Tableau.

(https://i.sstatic.net/mbTQ9.png)

that's not how I want it.

Then I tried this;

to_char(to_date(date_required, 'DD-MM-YYYY'), 'Month') As Base_Month

It gives me this error;

[Informix][Informix ODBC Driver][Informix]It is not possible to convert between the specified types. [Informix][Informix ODBC Driver][Informix]Invalid cursor received by sqlexec.

I have tried almost every solution but no help.


Solution

  • Using Informix Version 14.10.FC10DE, we can do:

    CREATE TABLE my_dates
    (
        date_required DATE
    );
    
    INSERT INTO my_dates VALUES ( '2023-08-20' );
    INSERT INTO my_dates VALUES ( '2023-01-10' );
    

    Then we can use the function "TO_CHAR" to get the month name:

    SELECT date_required, TO_CHAR( date_required, '%B') AS base_month FROM my_dates;
    
    date_required  2023-08-20
    base_month     August
    
    date_required  2023-01-10
    base_month     January
    

    From the online documentation:

    Symbol  Meaning
    %B      Full month name, as defined in the locale