Solved

oracle date conversion

Posted on 2014-11-21
24
263 Views
Last Modified: 2014-11-21
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
Comment
Question by:anumoses
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 7
  • 7
  • +2
24 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40457260
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
 
LVL 52

Expert Comment

by:Huseyin KAHRAMAN
ID: 40457444
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
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 40457521
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 74

Expert Comment

by:sdstuber
ID: 40457671
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
 
LVL 6

Author Comment

by:anumoses
ID: 40457807
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
 
LVL 52

Expert Comment

by:Huseyin KAHRAMAN
ID: 40457832
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
 
LVL 52

Expert Comment

by:Huseyin KAHRAMAN
ID: 40457840
also added LAST_DAY to the function ^^^
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 40457855
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
 
LVL 6

Author Comment

by:anumoses
ID: 40457867
aug2017

201708

Not working. For both formats this is what I get.
0
 
LVL 6

Author Comment

by:anumoses
ID: 40457911
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
 
LVL 52

Expert Comment

by:Huseyin KAHRAMAN
ID: 40457924
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
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 40457927
What error do the users get?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40457929
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
 
LVL 52

Expert Comment

by:Huseyin KAHRAMAN
ID: 40457930
"But user will scan this in the form. Its not working." need more info on this... what error message, what code?
0
 
LVL 6

Author Comment

by:anumoses
ID: 40457935
No error I think its not converting to last day of the month. Data stays as 201708 or aug2017
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40457940
If you are still having problems. Please be specific which code you are executing there are multiple versions posted above.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40457943
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
 
LVL 6

Author Comment

by:anumoses
ID: 40457945
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
 
LVL 52

Expert Comment

by:Huseyin KAHRAMAN
ID: 40457947
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
 
LVL 6

Author Comment

by:anumoses
ID: 40457952
Yes correct fast and many conversations.  The code you gave me worked. Thanks.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40457955
per 40457945

yes sorry about the original last_day confusion.

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

Expert Comment

by:sdstuber
ID: 40457967
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
 
LVL 6

Author Closing Comment

by:anumoses
ID: 40457994
Thanks a lot. you always provide the best solution.
0
 
LVL 52

Expert Comment

by:Huseyin KAHRAMAN
ID: 40458021
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to take different types of Oracle backups using RMAN.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question