5 Replies Latest reply: Feb 18, 2013 8:58 PM by Frank Kulash RSS

    How to get same day of a month in every year in the DB ( To update a flag )

    user10903866
      Hi,

      I am trying to formulate an update query for a flag table in our database which contains dates, and flag columns. Currently the system have dates for the next ten years. The flags are updated with values 0 or 1 if a particular date falls under the required criteria.

      I need to update flag column for the same day of the month in every year. e.g. 2nd Sunday of October. The value should be updated to all years in the table. Currently I am using the following query to update the current year.

      UPDATE FILTERCALENDAR SET YEAR_WINDOW=1 WHERE c_date = NEXT_DAY( TO_DATE('OCT-2013','MON-YYYY'), 'SUNDAY') + (2-1)*7;

      and for next year Like

      UPDATE FILTERCALENDAR SET YEAR_WINDOW=1 WHERE c_date = add_months(NEXT_DAY( TO_DATE('OCT-2013','MON-YYYY'), 'SUNDAY') + (2-1)*7,+12)-1;

      This is not an excellent way to do it as it does not take care of leap years and it does not scan and update values in the whole table for all years correctly.

      Can any one help me to resolve this please.
        • 1. Re: How to get same day of a month in every year in the DB ( To update a flag )
          rp0428
          Welcome to the forum!

          Whenever you post provide your 4 digit Oracle version (result of SELECT * FROM V$VERSION)
          >
          and for next year Like

          UPDATE FILTERCALENDAR SET YEAR_WINDOW=1 WHERE c_date = add_months(NEXT_DAY( TO_DATE('OCT-2013','MON-YYYY'), 'SUNDAY') + (2-1)*7,+12)-1;
          >
          Why are you using '2013' in the TO_DATE for next year instead of just using '2014'?

          You said this works
          >
          UPDATE FILTERCALENDAR SET YEAR_WINDOW=1 WHERE c_date = NEXT_DAY( TO_DATE('OCT-2013','MON-YYYY'), 'SUNDAY') + (2-1)*7;
          >
          Just query the years from your table (list of distinct years) and use the above query on them.
          SELECT DISTINCT TO_CHAR(c_date, 'yyyy') FROM FILTERCALENDAR
          Or use this predicate
          WHERE c_date = next_day(last_day(add_months (c_date, -1)), 'SUNDAY') + 7 
             AND TO_CHAR(c_date, 'mm') = '10'
          1. TO_CHAR makes sure the row has an October month.
          2. ADD_MONTHS goes to Sept.
          3. LAST_DAY goes to the last day of Sept.
          4. NEXT_DAY goes to the first Sunday of October
          5. +7 goes to the Second Sunday of October
          • 2. Re: How to get same day of a month in every year in the DB ( To update a flag )
            user10903866
            Actually I want to update the table in just one query where the user do not have to look at what years are there in the database.

            Is there is a way that I can run this query for all year values stored in the table without knowing how many are there, with taking care of the leap year. MAY BE through a loop or so. If you can give me a direction to do that I might achieve it as My SQL and PL/SQL skills are just novice.

            AND

            Sorry about not informing about the ORACLE version. Here it is.

            Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
            PL/SQL Release 11.2.0.3.0 - Production
            "CORE     11.2.0.3.0     Production"
            TNS for Linux: Version 11.2.0.3.0 - Production
            NLSRTL Version 11.2.0.3.0 - Production
            • 3. Re: How to get same day of a month in every year in the DB ( To update a flag )
              rp0428
              If you check the end of my reply above I showed you how to do that.

              What is it you don't understand?
              • 4. Re: How to get same day of a month in every year in the DB ( To update a flag )
                user10903866
                Thanks Heaps, You have saved my life :)
                • 5. Re: How to get same day of a month in every year in the DB ( To update a flag )
                  Frank Kulash
                  Hi,
                  user10903866 wrote:
                  Hi,

                  I am trying to formulate an update query for a flag table in our database which contains dates, and flag columns. Currently the system have dates for the next ten years. The flags are updated with values 0 or 1 if a particular date falls under the required criteria.

                  I need to update flag column for the same day of the month in every year. e.g. 2nd Sunday of October. The value should be updated to all years in the table. Currently I am using the following query to update the current year.

                  UPDATE FILTERCALENDAR SET YEAR_WINDOW=1 WHERE c_date = NEXT_DAY( TO_DATE('OCT-2013','MON-YYYY'), 'SUNDAY') + (2-1)*7;
                  That's the 2nd Sunday after October 1; the 2nd Sunday of October is the 2nd Sunday after September 30, so you need to subtract 1 more day before calling NEXT_DAY.
                  I'd do it this way:
                  WHERE   c_date = NEXT_DAY ( TO_DATE ( '01-OCT-2013', 'DD-MON-YYYY') - 8
                                           , 'SUNDAY'
                                   ) + (7 * 2)     -- Last number is week number
                  Remember, calling NEXT_DAY like this depends on your NLS_DATE_LANGUAGE.
                  and for next year Like

                  UPDATE FILTERCALENDAR SET YEAR_WINDOW=1 WHERE c_date = add_months(NEXT_DAY( TO_DATE('OCT-2013','MON-YYYY'), 'SUNDAY') + (2-1)*7,+12)-1;
                  If you want the 2nd Sunday in October, 2014, then take the previous expression, and just change 2013 to 2014:
                  WHERE   c_date = NEXT_DAY ( TO_DATE ( '01-OCT-2014'     -- or any month and year you want
                                                      , 'DD-MON-YYYY'
                                          ) - 8
                                           , 'SUNDAY'
                                   ) + (7 * 2)     -- Last number is week number
                  This is not an excellent way to do it as it does not take care of leap years and it does not scan and update values in the whole table for all years correctly.

                  Can any one help me to resolve this please.
                   

                  I hope this answers your question.
                  If not, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all the tables involved, and the results you want from that data.
                  In the case of a DML operation (such as UPDATE) the sample data should show what the tables are like before the DML, and the results will be the contents of the changed table(s) after the DML.
                  Explain, using specific examples, how you get those results from that data.
                  See the forum FAQ {message:id=9360002}