This discussion is archived
13 Replies Latest reply: Nov 21, 2012 8:53 PM by Purvesh K RSS

date string issue

9876564 Newbie
Currently Being Moderated
HI All,

I have a basic query
SELECT grn_d  
                FROM GRN WHERE ROWNUM < 2
The output of the above query is "5/15/1991 12:00:00 AM"

But when i am running the following block
DECLARE
grn_d_1 DATE;
BEGIN

SELECT grn_d INTO grn_d_1 
FROM GRN WHERE ROWNUM < 2;
dbms_output.put_line(grn_d_1);
END;
 
The output is in 15-MAY-91 this format , how to get the result in the same format i.e. MM/DD/YYYY.
  • 1. Re: date string issue
    Paul Horth Expert
    Currently Being Moderated
    AbSHeik wrote:
    HI All,

    I have a basic query
    SELECT grn_d  
    FROM GRN WHERE ROWNUM < 2
    The output of the above query is "5/15/1991 12:00:00 AM"

    But when i am running the following block
    DECLARE
    grn_d_1 DATE;
    BEGIN
    
    SELECT grn_d INTO grn_d_1 
    FROM GRN WHERE ROWNUM < 2;
    dbms_output.put_line(grn_d_1);
    END;
    The output is in 15-MAY-91 this format , how to get the result in the same format i.e. MM/DD/YYYY.
    To convert dates to a human-readable string you use to_char with a format mask:
    to_char(grn_d_1, 'mm/dd/yyyy')
    Edited by: Paul Horth on 21-Nov-2012 05:42
    Read that you wanted the other format - corrected.
  • 2. Re: date string issue
    ranit B Expert
    Currently Being Moderated
    AbSHeik wrote:
    HI All,

    I have a basic query
    SELECT grn_d  
    FROM GRN WHERE ROWNUM < 2
    The output of the above query is "5/15/1991 12:00:00 AM"

    But when i am running the following block
    DECLARE
    grn_d_1 DATE;
    BEGIN
    
    SELECT grn_d INTO grn_d_1 
    FROM GRN WHERE ROWNUM < 2;
    dbms_output.put_line(grn_d_1);
    END;
    The output is in 15-MAY-91 this format , how to get the result in the same format i.e. MM/DD/YYYY.
    Try this -
    TO_CHAR(grn_d_1,'MM/DD/YYYY')
  • 3. Re: date string issue
    AlbertoFaenza Expert
    Currently Being Moderated
    AbSHeik wrote:
    HI All,

    I have a basic query
    SELECT grn_d  
    FROM GRN WHERE ROWNUM < 2
    The output of the above query is "5/15/1991 12:00:00 AM"

    But when i am running the following block
    DECLARE
    grn_d_1 DATE;
    BEGIN
    
    SELECT grn_d INTO grn_d_1 
    FROM GRN WHERE ROWNUM < 2;
    dbms_output.put_line(grn_d_1);
    END;
    The output is in 15-MAY-91 this format , how to get the result in the same format i.e. MM/DD/YYYY.
    Hi Absheik,

    unless you convert your date to varchar and format it the default formatting is applied to date type variable. You can use alter session to set the default date format in your session.
    I.e.:
    SQL> SELECT SYSDATE FROM DUAL;
    
    SYSDATE
    ---------
    21-NOV-12
    
    SQL> 
    SQL> SELECT TO_CHAR(SYSDATE, 'MM/DD/YYYY') FROM DUAL;
    
    TO_CHAR(SY
    ----------
    11/21/2012
    
    SQL> 
    SQL> ALTER SESSION SET NLS_DATE_FORMAT='MM/DD/YYYY';
    
    Session altered.
    
    SQL> 
    SQL> SELECT SYSDATE FROM DUAL;
    
    SYSDATE
    ----------
    11/21/2012
    Regards.
    Al
  • 4. Re: date string issue
    9876564 Newbie
    Currently Being Moderated
    Paul Horth wrote:
    AbSHeik wrote:
    HI All,

    I have a basic query
    SELECT grn_d  
    FROM GRN WHERE ROWNUM < 2
    The output of the above query is "5/15/1991 12:00:00 AM"

    But when i am running the following block
    DECLARE
    grn_d_1 DATE;
    BEGIN
    
    SELECT grn_d INTO grn_d_1 
    FROM GRN WHERE ROWNUM < 2;
    dbms_output.put_line(grn_d_1);
    END;
    The output is in 15-MAY-91 this format , how to get the result in the same format i.e. MM/DD/YYYY.
    To convert dates to a human-readable string you use to_char with a format mask:
    to_char(grn_d_1, 'mm/dd/yyyy')
    Edited by: Paul Horth on 21-Nov-2012 05:42
    Read that you wanted the other format - corrected.
    My Question is ; i want to store the output into date variable only
    Let me put my requirement , i am getting two dates from two different queries and forming the "least" clause dynamically (kindly dont ask me to not to use dynamic sql as it is required )
    DECLARE 
    v_d1 DATE;
    v_d2 DATE;
    v_d3 DATE;
    v_cha VARCHAR2(20000);
    BEGIN
    
    
    
    SELECT to_date('11-SEP-2090','DD-MON-YYYY') INTO v_d1 FROM DUAL ;
    SELECT to_date('12-SEP-1990','DD-MON-YYYY') INTO v_d2 FROM DUAL ;
    v_cha := 'SELECT least('''||v_d1||''','''||v_d2||''')  FROM DUAL';
    EXECUTE IMMEDIATE v_cha INTO v_d3;
    dbms_output.PUT_LINE(v_d3);
    END;
    output is not as expected it should be '12-SEP-1990' but the actual is '11-SEP-2090'.

    Edited by: AbSHeik on Nov 21, 2012 6:04 AM
  • 5. Re: date string issue
    theoa Pro
    Currently Being Moderated
    AbSHeik wrote:
    output is not as expected it should be '12-SEP-1990' but the actual is '11-SEP-2090'.
    That is because you use implicit date conversions in your dynamic sql.
    Don't. Use bind-variables instead:
    DECLARE 
      v_d1 DATE;
      v_d2 DATE;
      v_d3 DATE;
      v_cha VARCHAR2(20000);
    BEGIN
      SELECT to_date('11-SEP-2090','DD-MON-YYYY') INTO v_d1 FROM DUAL ;
      SELECT to_date('12-SEP-1990','DD-MON-YYYY') INTO v_d2 FROM DUAL ;
      v_cha := 'SELECT least(:1, :2)  FROM DUAL';
      EXECUTE IMMEDIATE v_cha INTO v_d3 USING v_d1, v_d2;
      dbms_output.PUT_LINE(to_char(v_d3, 'dd-MON-yyyy'));
    END;
  • 6. Re: date string issue
    bencol Pro
    Currently Being Moderated
    You are implicitly converting the date variables to strings, try binding:

    Untested
    v_cha := 'SELECT least(:1,:2)  FROM DUAL';
    
    EXECUTE IMMEDIATE v_cha 3 INTO v_d3  USING v_d1,v_d2;
    Edited by: bencol on Nov 21, 2012 2:08 PM
  • 7. Re: date string issue
    AlbertoFaenza Expert
    Currently Being Moderated
    Hi AbSheik,

    I don't understand how this relates with the initial question:
    The output is in 15-MAY-91 this format , how to get the result in the same format i.e. MM/DD/YYYY.
    Now you are asking something different. Please be clear.

    Regards.
    Al
  • 8. Re: date string issue
    9876564 Newbie
    Currently Being Moderated
    I am agree that with "using" clause the problem can be solved but in my case the number of parameters in "using" clause is not fixed so i have to create that dynamically and need to run the execute immediate with that , again i dont think the below is the correct way of doing that as it is throwing "all the parameters are not bound" error.
    DECLARE 
      v_d1 DATE;
      v_d2 DATE;
       v_d3 DATE;
      v_cha VARCHAR2(20000);
      v_vha VARCHAR2(20000);
    BEGIN
      v_d1:= to_date('01-JUN-15','DD-MON-YY'); 
      v_d2 :=to_date('15-FEB-14','DD-MON-YY');
      
      v_cha := 'SELECT least(:1, :2)  FROM DUAL';
      v_vha := ''''||v_d1||''','''||v_d2||'''';
      EXECUTE IMMEDIATE v_cha INTO v_d3 USING v_vha;
      dbms_output.PUT_LINE(to_char(v_d3, 'dd-MON-yyyy'));
    END;
    Please help me out in getting the above block run.

    Edited by: AbSHeik on Nov 21, 2012 8:08 PM
  • 9. Re: date string issue
    Purvesh K Guru
    Currently Being Moderated
    AbSHeik wrote:
    I am agree that with "using" clause the problem can be solved but in my case the number of parameters in "using" clause is not fixed so i have to create that dynamically and need to run the execute immediate with that , again i dont think the below is the correct way is doing that as it is throwing that all the parameters are not bound error.
    Yes, you are correct in thinking that below is not the correct way.

    Because of (Extract from below linked documentation)
    +"At run time, bind arguments replace corresponding placeholders in the dynamic string. Every placeholder must be associated with a bind argument in the USING clause and/or RETURNING INTO clause."+

    For more info read Oracle Execute Immediate.

    To execute the block, you will have to pass the Place holders specifically; Concatenating it into a string would not work AFAIK.

    Even though, you exert on using Dynamic SQL, the example atleast does not convince me that the use is absolutely essential. If LEAST is what you have to perform it works without dynamic sql too, isn't it?
  • 10. Re: date string issue
    9876564 Newbie
    Currently Being Moderated
    Purvesh K wrote:
    AbSHeik wrote:
    I am agree that with "using" clause the problem can be solved but in my case the number of parameters in "using" clause is not fixed so i have to create that dynamically and need to run the execute immediate with that , again i dont think the below is the correct way is doing that as it is throwing that all the parameters are not bound error.
    Yes, you are correct in thinking that below is not the correct way.

    Because of (Extract from below linked documentation)
    +"At run time, bind arguments replace corresponding placeholders in the dynamic string. Every placeholder must be associated with a bind argument in the USING clause and/or RETURNING INTO clause."+

    For more info read Oracle Execute Immediate.

    To execute the block, you will have to pass the Place holders specifically; Concatenating it into a string would not work AFAIK.

    Even though, you exert on using Dynamic SQL, the example atleast does not convince me that the use is absolutely essential. If LEAST is what you have to perform it works without dynamic sql too, isn't it?
    Thanks Purvesh.
    The example which i have presented here is just to give an idea.
    In my requirement i need to find the least date among number of dates those are coming from the system , there can be one date or more than one.
    So i need to do all my processing dynamically and get the least date processed for the further calcualtion , i hope u understand , when u dont have fixed number of parameters than u cant pass the Place holders specifically.
    Kindly rethink?

    Edited by: AbSHeik on Nov 21, 2012 8:18 PM
  • 11. Re: date string issue
    sb92075 Guru
    Currently Being Moderated
    AbSHeik wrote:
    In my requirement i need to find the least date among number of dates those are coming from the system , there can be one date or more than one.
    how can there be more than one "LEAST DATE"?
  • 12. Re: date string issue
    Ashu_Neo Pro
    Currently Being Moderated
    This might help you to understand. Check my comments and run it.
         
    DECLARE 
      v_d1 DATE;
      v_d2 DATE;
      v_d3 DATE;
      v_cha VARCHAR2(20000);
     -- v_vha VARCHAR2(20000); -- commented due to duplicate variable intialisation
     str VARCHAR2(20000); -- Added new
    BEGIN
      v_d1:= to_date('01-JUN-15','DD-MON-YY'); 
      v_d2 :=to_date('15-FEB-14','DD-MON-YY');
      
      v_cha := 'SELECT least(:1, :2)  FROM DUAL';
      --v_vha := ''''||v_d1||''','''||v_d2||'''';
      --EXECUTE IMMEDIATE v_cha INTO v_d3 USING v_vha; -- cant declare like this after USING clause a comma separated string variable
      EXECUTE IMMEDIATE v_cha INTO v_d3 USING v_d1,v_d2;
      dbms_output.PUT_LINE('v_d3 - 1 '||to_char(v_d3, 'dd-MON-yyyy'));
      dbms_output.PUT_LINE('v_d3 - 2 '||to_char(v_d3,'MM/DD/YYYY'));
      dbms_output.PUT_LINE('v_d3 - 3 '||v_d3);
      dbms_output.PUT_LINE('--------------------------------');
      dbms_output.PUT_LINE('-- If you not sure about no of bind variables then dont use USING clause, make it a one string to be executed dynamically --');
      str := 'SELECT least(to_date('''||v_d1||'''),to_date('''||v_d2||'''))  FROM DUAL'; -- Due to implicit conversion, here you have use to_date while preparing a stmt
      EXECUTE IMMEDIATE str INTO v_d3;  -- while executing dynamically it'll consider date as date not string due to to_date function
      dbms_output.PUT_LINE('v_d3 - 1 '||to_char(v_d3, 'dd-MON-yyyy'));
      dbms_output.PUT_LINE('v_d3 - 2 '||to_char(v_d3,'MM/DD/YYYY'));
      dbms_output.PUT_LINE('v_d3 - 3 '||v_d3);
    END;
    /
    
    OP :- 
    v_d3 - 1 15-FEB-2014
    v_d3 - 2 02/15/2014
    v_d3 - 3 15-FEB-14
    --------------------------------
    -- If you are not sure about no of bind variables then dont use USING clause, make
    it a one string to be executed dynamically --
    v_d3 - 1 15-FEB-2014
    v_d3 - 2 02/15/2014
    v_d3 - 3 15-FEB-14
    
    PL/SQL procedure successfully completed.
    Thanks!
  • 13. Re: date string issue
    Purvesh K Guru
    Currently Being Moderated
    AbSHeik wrote:
    The example which i have presented here is just to give an idea.
    In my requirement i need to find the least date among number of dates those are coming from the system , there can be one date or more than one.
    So i need to do all my processing dynamically and get the least date processed for the further calcualtion , i hope u understand , when u dont have fixed number of parameters than u cant pass the Place holders specifically.
    Kindly rethink?
    In such a scenario I will suggest use of Collections.

    Dynamic SQL isn't suggested unless you are not sure of Object Names until runtime. This scenario is just getting variable Dates and to find the Least.

    So, you can feed all the dates into a Collection and then Order the Collection in Ascending fashion to pick the Top element.
    You can also feed all the dates into a Global Temp Table and fire a SQL to fetch the Least of dates.

    I will still suggest you to avoid the Dynamic SQL, at least until you have an alternative.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points