2 Replies Latest reply: Nov 17, 2012 1:17 AM by jeneesh RSS

    Extract function

    9876564
      Hi All,

      I have a piece of code
        
      DECLARE
      V_DOB DATE := '29-FEB-2000';
      v_don1 DATE;
      BEGIN
      
      SELECT to_date('2003'||lpad(extract(Month FROM V_DOB),2,0)||extract(Day FROM V_DOB), 'YYYYMMDD') into v_don1
      FROM DUAL;
      dbms_output.put_line(v_don1);
      END;
      The above code is giving error, it is working fine with V_dob as '28-FEB-2000'.
      Year 2000 was a leap year so date 29 should be there.

      Kindly suggest me what to do?
       oracle version : 11.2.0.2.0 
      Thanks

      Edited by: AbSHeik on Nov 16, 2012 10:41 PM
        • 1. Re: Extract function
          jeneesh
          2003 is not a leap year..

          The below will work
          DECLARE
          V_DOB DATE := '29-FEB-2000';
          v_don1 DATE;
          BEGIN
           
          SELECT to_date('2004'||lpad(extract(Month FROM V_DOB),2,0)|| --"changed to 2004"
                extract(Day FROM V_DOB), 'YYYYMMDD') into v_don1
          FROM DUAL;
          dbms_output.put_line(v_don1);
          END;
          between, what are you trying to do?

          Edited by: jeneesh on Nov 17, 2012 12:26 PM
          • 2. Re: Extract function
            jeneesh
            And few more things:
            DECLARE
              --"Whenever you assign to a date value, dont assign as string
              --"use TO_DATE, else your code will have a dependency on NLS settings
              /*V_DOB DATE := '29-FEB-2000';*/
              V_DOB DATE := to_date('29-FEB-2000','DD-MON-YYYY');
              v_don1 DATE;
            BEGIN
              /*SELECT to_date('2003'||lpad(extract(Month FROM V_DOB),2,0)||
                    extract(Day FROM V_DOB), 'YYYYMMDD') into v_don1
              FROM DUAL;*/
              --"You dont need to use SELECT as in above code"
              --"Directly you can assign as below"
              v_don1 := to_date('2004'||
                                lpad(extract(Month FROM V_DOB),2,0)||
                                extract(Day FROM V_DOB), 'YYYYMMDD'
                               );
              --"And, whenever you are printing a date, use TO_CHAR
              /*dbms_output.put_line(v_don1);*/
              dbms_output.put_line(to_char(v_don1,'DD-Mon-YYYY'));
            END;
            {code}
            
            And if your objective is to find out the last day of the same month of a different year, you can use LAST_DAY function
            
            {code}
            DECLARE
              V_DOB DATE := to_date('29-FEB-2000','DD-MON-YYYY');
              v_don1 DATE;
            BEGIN
              v_don1 := last_day(to_date('2003'||
                                lpad(extract(Month FROM V_DOB),2,0)||
                                '01', 'YYYYMMDD'
                               ));
              dbms_output.put_line(to_char(v_don1,'DD-Mon-YYYY'));
            END;
            
            28-Feb-2003
            
            {code}
            
            {code}
            
            Edited by: jeneesh on Nov 17, 2012 12:46 PM