Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 315
  • Last Modified:

oracle date conversion

1 ) Date format the user scans 201708

I convert that date to the last date of the month

:supplies_received.exp_date_conv := to_date(:supplies_received.exp_date_conv,'RRRRMM');

:supplies_received.exp_date_conv := TO_CHAR(LAST_DAY(:supplies_received.exp_date_conv),'DD-MON-RRRR');

2 ) Date format the user scans AUG2017

I convert that date to the last date of the month

:supplies_received.exp_date_conv := to_date(:supplies_received.exp_date_conv,'MONRRRR');

:supplies_received.exp_date_conv := TO_CHAR(LAST_DAY(:supplies_received.exp_date_conv),'DD-MON-RRRR');

I need to allow the user to scan both formats. Then convert the date to the last day of the month.
 Need help here to allow users to scan either of the formats.

supplies_received.exp_date_conv is varchar2(11)
0
anumoses
Asked:
anumoses
  • 7
  • 7
  • 7
  • +2
1 Solution
 
slightwv (䄆 Netminder) Commented:
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;
0
 
HainKurtSr. System AnalystCommented:
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;
0
 
Mark GeerlingsDatabase AdministratorCommented:
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');
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
sdstuberCommented:
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;
/

Open in new window



You could change the RETURN NULL;  to raise an exception instead if no valid format is found for the input string.
0
 
anumosesAuthor Commented:
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
0
 
HainKurtSr. System AnalystCommented:
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;
/

Open in new window

0
 
HainKurtSr. System AnalystCommented:
also added LAST_DAY to the function ^^^
0
 
sdstuberCommented:
31-36 are redundant only if you want to keep default to NULL and you don't plan on returning the correct data type.

written the way I originally posted does the same thing while self-documenting why the result is NULL as well as providing the framework to change it to an exception or magic value or log it or do any other appropriate action for invalid inputs.

yes 17 is not needed

I have no idea why LAST_DAY isn't in my code.  I tested with it, and then pasted the wrong version.  Sorry about that


Here is my corrected version.
Note, it's not necessary to attempt to find last day for every intermediate value, only on those that succeed.  However, if they fail the call wouldn't have been made so functionally the same.

Just stylistically I prefer this way

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
        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(LAST_DAY(v_temp), 'DD-MON-YYYY');
    ELSE
        RETURN NULL;
    END IF;
END;
/

Open in new window



also note, I intentionally changed RRRR to YYYY in order to encourage full year entry.

If however you need to support 2 digit years then try this...

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', 'MONRRRR','RRMM');
BEGIN
    WHILE v_index <= v_formats.COUNT AND NOT v_ok
    LOOP
        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(LAST_DAY(v_temp), 'DD-MON-YYYY');
    ELSE
        RETURN NULL;
    END IF;
END;
/

Open in new window

0
 
anumosesAuthor Commented:
aug2017

201708

Not working. For both formats this is what I get.
0
 
anumosesAuthor Commented:
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.
0
 
HainKurtSr. System AnalystCommented:
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

Open in new window

0
 
Mark GeerlingsDatabase AdministratorCommented:
What error do the users get?
0
 
sdstuberCommented:
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
0
 
HainKurtSr. System AnalystCommented:
"But user will scan this in the form. Its not working." need more info on this... what error message, what code?
0
 
anumosesAuthor Commented:
No error I think its not converting to last day of the month. Data stays as 201708 or aug2017
0
 
sdstuberCommented:
If you are still having problems. Please be specific which code you are executing there are multiple versions posted above.
0
 
sdstuberCommented:
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
0
 
anumosesAuthor Commented:
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
0
 
HainKurtSr. System AnalystCommented:
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...
0
 
anumosesAuthor Commented:
Yes correct fast and many conversations.  The code you gave me worked. Thanks.
0
 
sdstuberCommented:
per 40457945

yes sorry about the original last_day confusion.

I'm glad you found my correction amidst the rest of the thread
0
 
sdstuberCommented:
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.
0
 
anumosesAuthor Commented:
Thanks a lot. you always provide the best solution.
0
 
HainKurtSr. System AnalystCommented:
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)...
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 7
  • 7
  • 7
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now