6 Replies Latest reply: Jun 23, 2011 1:59 PM by Solomon Yakobson RSS

    ORA-01821: date format not recognized

    845811
      Hi all,

      I am new to oracle development.
      Can you please help me with the below error.



      create or replace procedure d_format(p_date_format in varchar2)

      as

      v_date_format varchar2(30) := p_date_format;
      v_next_year_first_day varchar2(50);

      begin

      v_next_year_first_day:=to_char(last_day(add_months(trunc(sysdate),12))+1,'v_date_format');

      dbms_output.put_line(v_next_year_first_day||' next year first day');

      end;
      /





      BEGIN
      D_FORMAT ( 'yyyymmdd' );
      COMMIT;
      END;

      Error at line 1
      ORA-01821: date format not recognized
      ORA-06512: at "D_FORMAT", line 10
      ORA-06512: at line 4


      My requirement is to pass the date format like YYYYMMDD, MMDDYYY , YYDDMM ,DD-MON-YYYY etc.. as parameter in the procedure.

      Thanks in advance.
        • 1. Re: ORA-01821: date format not recognized
          sb92075
          user13517642 wrote:
          Hi all,

          I am new to oracle development.
          Can you please help me with the below error.



          create or replace procedure d_format(p_date_format in varchar2)

          as

          v_date_format varchar2(30) := p_date_format;
          v_next_year_first_day varchar2(50);

          begin

          v_next_year_first_day:=to_char(last_day(add_months(trunc(sysdate),12))+1,'v_date_format');

          dbms_output.put_line(v_next_year_first_day||' next year first day');

          end;
          /





          BEGIN
          D_FORMAT ( 'yyyymmdd' );
          COMMIT;
          END;

          Error at line 1
          ORA-01821: date format not recognized
          ORA-06512: at "D_FORMAT", line 10
          ORA-06512: at line 4


          My requirement is to pass the date format like YYYYMMDD, MMDDYYY , YYDDMM ,DD-MON-YYYY etc.. as parameter in the procedure.
          WHY does this "requirement" exist?

          What problem are you actually trying to solve?
          • 2. Re: ORA-01821: date format not recognized
            Ganesh Srivatsav
            You dont need the quotes around that variable,
            CREATE OR REPLACE PROCEDURE d_format (p_date_format IN VARCHAR2)
            AS
               v_date_format           VARCHAR2 (30) := p_date_format;
               v_next_year_first_day   VARCHAR2 (50);
            BEGIN
               v_next_year_first_day := TO_CHAR ( LAST_DAY (ADD_MONTHS ( TRUNC (SYSDATE), 12)) + 1, v_date_format);
            
               DBMS_OUTPUT.PUT_LINE (v_next_year_first_day || ' next year first day');
            END;
            /
            
            
            SQL> set serverout on
            SQL> BEGIN 
              2  D_FORMAT ( 'yyyymmdd' );
              3  COMMIT; 
              4  END;
              5  /
            20120701 next year first day
            
            PL/SQL procedure successfully completed.
            
            SQL> 
            • 3. Re: ORA-01821: date format not recognized
              845811
              Hi sb90275,

              Actually this is part of table maintenance script, where I would like to pass the format as a parameter.....so that this script can be used for multiple schema's where partition date formats may be different....


              ie

              i would like to use

              v_next_year_first_day:=to_char(last_day(add_months(trunc(sysdate),12))+1,'v_date_format');


              instead of

              v_next_year_first_day:=to_char(last_day(add_months(trunc(sysdate),12))+1,'yyyymmdd');

              Thanks.
              • 4. Re: ORA-01821: date format not recognized
                Solomon Yakobson
                By enclosing 'v_date_format' in quotes you turned it into a literal, instead of variable. And you do not need v_date_format variable at all.:
                SQL> create or replace procedure d_format(p_date_format in varchar2)
                  2  as 
                  3  v_next_year_first_day varchar2(50);
                  4  begin
                  5  v_next_year_first_day:=to_char(last_day(add_months(trunc(sysdate),12))+1,p_date_format);
                  6  dbms_output.put_line(v_next_year_first_day||' next year first day');
                  7  end;
                  8  /
                
                Procedure created.
                
                SQL> set serveroutput on
                SQL> BEGIN 
                  2  D_FORMAT ( 'yyyymmdd' );
                  3  end;
                  4  /
                20120701 next year first day
                
                PL/SQL procedure successfully completed.
                
                SQL> 
                And if you intended to get first day (January 1) of next year:
                SQL> create or replace procedure d_format(p_date_format in varchar2)
                  2  as 
                  3  v_next_year_first_day varchar2(50);
                  4  begin
                  5  v_next_year_first_day:=to_char(add_months(trunc(sysdate,'yy'),12),p_date_format);
                  6  dbms_output.put_line(v_next_year_first_day||' next year first day');
                  7  end;
                  8  /
                Procedure created.
                
                SQL> BEGIN 
                  2  D_FORMAT ( 'yyyymmdd' );
                  3  end;
                  4  /
                20120101 next year first day
                
                PL/SQL procedure successfully completed.
                
                SQL> 
                SY.
                • 5. Re: ORA-01821: date format not recognized
                  845811
                  Thank you Ganesh and Solomon.

                  Solomon I have the following question,

                  v_next_year_first_day:=to_char(add_months(trunc(sysdate,'yy'),12),p_date_format); this indeed returns the jan 1 of next year...

                  my logic for monthly is..


                  --to get string for first day of following month.
                  --example: 09-01-2005 when you want to create the August partition

                  v_next_month_first_day:=to_char(last_day(add_months(trunc(TODAY),1))+1,p_date_format);


                  --get string for next month to be used as part of partition name
                  --example: AUG_2005

                  v_next_month:=to_char(add_months(sysdate,1),p_date_format);


                  --get string for old month to be used as part of partition name
                  --example: AUG_2005

                  v_old_month:=to_char(add_months(sysdate,-(months_retention+1)),p_date_format);

                  v_months_retention= parameter to determine the retention time for a partition..value will in months


                  So based on the above logic for monthly. What is the best possible way to implement that for

                  1. Yearly
                  2. Daily

                  ie for YEARLY,

                  v_next_year_first_day:=to_char(add_months(trunc(sysdate,'yy'),12),v_date_format);....as you said...

                  my code for getting the next year from today

                  v_next_year_mon:=to_char(add_months(sysdate,12),'v_date_format'); -- is this right...or is there a better way?

                  v_old_year:=to_char(add_months(TODAY,-(v_months_retention+1)),'v_date_format'); --- this is for last / old year

                  v_months_retention= parameter to determine the retention time for a partition in months....

                  .....is this right to get v_old_year?


                  DAILY

                  -get string for day ahead of the follwing day .

                  --example: 09-01-2005 when you want to create the 07/01/2005 partition

                  v_next_day_check:=to_char((TODAY+2),v_date_format);----


                  --get string for next day to be used as part of partition name

                  v_next_day:=to_char((TODAY+1),v_date_format);


                  --get string for old dayto be used as part of partition name

                  v_old_day:=to_char(add_months(TODAY,-(v_months_retention+1)),v_date_format);

                  Is this the right way to go about it.....

                  These variables I intend to use in a partition maintenance script whre the basic idea is

                  v_next_first-----will get the value for 1st day of next month/ year.....v_next_day_check for daily

                  v_next_month/year/day is for next month ,year, day

                  v_old mon/year/day is for previous mon,year and day....

                  Please suggest if any other correct / efficient ways to compute the above variables.

                  Thanks in Advance.
                  • 6. Re: ORA-01821: date format not recognized
                    Solomon Yakobson
                    1. Daily. To get beginning of the next day:
                    TRUNC(SYSDATE + 1)
                    2. Monthly. To get beginning of the first day of the following month:
                    TRUNC(LAST_DAY(SYSDATE) + 1)
                    SY.