13 Replies Latest reply: Nov 21, 2012 10:53 PM by Purvesh K RSS

    date string issue

    9876564
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              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
                                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
                                  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.