Solved

oracle date conversion

Posted on 2014-11-21
24
245 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
  • 7
  • 7
  • 7
  • +2
24 Comments
 
LVL 76

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 51

Expert Comment

by:HainKurt
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 34

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
 
LVL 73

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 51

Expert Comment

by:HainKurt
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 51

Expert Comment

by:HainKurt
ID: 40457840
also added LAST_DAY to the function ^^^
0
 
LVL 73

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 51

Expert Comment

by:HainKurt
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 34

Expert Comment

by:Mark Geerlings
ID: 40457927
What error do the users get?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 73

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 51

Expert Comment

by:HainKurt
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 73

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 73

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 51

Expert Comment

by:HainKurt
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 73

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 73

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 51

Expert Comment

by:HainKurt
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

747 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now