This discussion is archived
2 Replies Latest reply: Nov 16, 2012 11:17 PM by jeneesh RSS

Extract function

9876564 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    

Legend

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