oracle date conversion

Try a CASE statement.

Something like this:
select last_day(case when substr(:supplies_received.exp_date_conv,1,1) = '2' then to_date(:supplies_received.exp_date_conv,'RRRRMM') else to_date(:supplies_received.exp_date_conv,'MONRRRR') end) from dual;

maybe this is what you are looking for:

if translate(:supplies_received.exp_date_conv,'0123456789','') != ''
then
  format according to logic 1 here
else
  format according to logic 2 here
end if;

You don't need the two-step approach for each date format that you posted in the question (these could each be combined into one command that includes: "to_date", "last_day" and "to_char").  And, the suggestion from slightwv doesn't include the outer "to_char" that you asked for to get the results into a varchar2 variable/column in the format you requested.  But, that would be easy to add to his suggestion, like this:

:supplies_received.exp_date_conv := to_char(last_day(case when substr(:supplies_received.exp_date_conv,1,1) = '2' then to_date(:supplies_received.exp_date_conv,'RRRRMM') else to_date(:supplies_received.exp_date_conv,'MONRRRR'), 'DD-MON-RRRR');

I would create function like the one below that way it's easy to support other formats as needed without complicating the assignment.

:supplies_received.exp_date_conv := get_last_day(:supplies_received.exp_date_conv);

CREATE OR REPLACE FUNCTION get_last_day(p_str IN VARCHAR2)
    RETURN VARCHAR2
IS
    TYPE format_types IS TABLE OF VARCHAR2(50);

    v_temp    DATE;
    v_ok      BOOLEAN := FALSE;
    v_index   INTEGER := 1;

    -- To support more formats, just append them to this list
    -- Use whatever order you want to process them in
    -- The first one that matches will be used.
    v_formats format_types := format_types('YYYYMM', 'MONYYYY');
BEGIN
    WHILE v_index <= v_formats.COUNT AND NOT v_ok
    LOOP
        v_ok := FALSE;

        BEGIN
            v_temp := TO_DATE(p_str, v_formats(v_index));
            v_ok := TRUE;
        EXCEPTION
            WHEN OTHERS
            THEN
                v_ok := FALSE;
        END;

        v_index := v_index + 1;
    END LOOP;

    IF v_ok
    THEN
        RETURN TO_CHAR(v_temp, 'DD-MON-YYYY');
    ELSE
        RETURN NULL;
    END IF;
END;
/

Select allOpen in new window

You could change the RETURN NULL;  to raise an exception instead if no valid format is found for the input string.

using the above function I am getting 1st day of the month when I scan in 201708 instead of 31-aug-2017,  I get 01-aug-2017

i suggest, if you go with a function ^^^,
line 17 is redundant, and you should return a date not string, so you can format the returned value later whatever you
want... also line 31-36 looks redundant to me...

here what I mean:

CREATE OR REPLACE FUNCTION get_last_day(p_str IN VARCHAR2)
    RETURN DATE
IS
    TYPE format_types IS TABLE OF VARCHAR2(50);

    v_temp    DATE := NULL;
    v_ok      BOOLEAN := FALSE;
    v_index   INTEGER := 1;

    -- To support more formats, just append them to this list
    -- Use whatever order you want to process them in
    -- The first one that matches will be used.
    v_formats format_types := format_types('YYYYMM', 'MONYYYY');
BEGIN
    WHILE v_index <= v_formats.COUNT AND NOT v_ok
    LOOP
        BEGIN
            v_temp := LAST_DAY(TO_DATE(p_str, v_formats(v_index)));
            v_ok := TRUE;
        EXCEPTION
            WHEN OTHERS
            THEN
                NULL;
        END;

        v_index := v_index + 1;
    END LOOP;

    RETURN v_temp;
END;
/

Select allOpen in new window

also added LAST_DAY to the function ^^^

aug2017

201708

Not working. For both formats this is what I get.

When I run this in toad or sql plus it works

select get_last_day('201708') from dual

8/31/2017

But user will scan this in the form. Its not working.

FUNCTION get_last_day (p_str IN VARCHAR2)
    RETURN DATE
  IS
    TYPE format_types IS TABLE OF VARCHAR2 (50);

    v_temp      DATE := NULL;
    v_ok        BOOLEAN := FALSE;
    v_index     INTEGER := 1;

    -- To support more formats, just append them to this list
    -- Use whatever order you want to process them in
    -- The first one that matches will be used.
    v_formats   format_types := format_types ('YYYYMM', 'MONYYYY');
  BEGIN
    WHILE v_index <= v_formats.COUNT AND NOT v_ok
    LOOP
      BEGIN
        v_temp := LAST_DAY (TO_DATE (p_str, v_formats (v_index)));
        v_ok := TRUE;
      EXCEPTION
        WHEN OTHERS
        THEN
          NULL;
      END;

      v_index := v_index + 1;
    END LOOP;

    RETURN v_temp;
  END;

select get_last_day('201708') test1,  get_last_day('AUG2017') test2 from dual

TEST1	TEST2
8/31/2017	8/31/2017

Select allOpen in new window

What error do the users get?

I assume by "THIS" you mean HainKurt's version of my code - he changed my code to return a date (both of his versions do this), not the string you were looking for.

Try my last version ( http:#a40457855 ), it returns the text output you were looking for in the format you wanted

"But user will scan this in the form. Its not working." need more info on this... what error message, what code?

No error I think its not converting to last day of the month. Data stays as 201708 or aug2017

If you are still having problems. Please be specific which code you are executing there are multiple versions posted above.

anumoses - the conversation is moving very fast - I think you missed the corrected version I posted above.

Please try the first code block in  http:#a40457855

it does what you asked for

CREATE OR REPLACE FUNCTION get_last_day(p_str IN VARCHAR2)
    RETURN VARCHAR2
IS
    TYPE format_types IS TABLE OF VARCHAR2(50);

    v_temp    DATE;
    v_ok      BOOLEAN := FALSE;
    v_index   INTEGER := 1;

    -- To support more formats, just append them to this list
    -- Use whatever order you want to process them in
    -- The first one that matches will be used.
    v_formats format_types := format_types('YYYYMM', 'MONYYYY');
BEGIN
    WHILE v_index <= v_formats.COUNT AND NOT v_ok
    LOOP
        v_ok := FALSE;

        BEGIN
            v_temp := TO_DATE(p_str, v_formats(v_index));
            v_ok := TRUE;
        EXCEPTION
            WHEN OTHERS
            THEN
                v_ok := FALSE;
        END;

        v_index := v_index + 1;
    END LOOP;

    IF v_ok
    THEN
        RETURN TO_CHAR(v_temp, 'DD-MON-YYYY');
    ELSE
        RETURN NULL;
    END IF;
END;

This worked

i guess you are using this:

:supplies_received.exp_date_conv := get_last_day (:supplies_received.exp_date_conv);

without any condition on your form...

Yes correct fast and many conversations.  The code you gave me worked. Thanks.

per 40457945

yes sorry about the original last_day confusion.

I'm glad you found my correction amidst the rest of the thread

per 40457947

HainKurt - the problem was when you modified my code you changed the return type.

The IF at the end wasn't just about nulls, it was also to ensure the results, if found, would be converted to text and formatted correctly

By returning a date type, the users would then get an implicit conversion from your date into the form's varchar2 variable, same problem when querying it directly in sql, what she saw was the default implicit formatting.

Thanks a lot. you always provide the best solution.

and no point for me :) wasted so much time on this...

I changed the return type thinking you are giving a generic solution that can be used anywhere... so returning date and formatting on where it is used was a good idea...

otherwise, this problem can be solved without a function easily (see my first post, a solution with "translate" function)...