13 Replies Latest reply: Apr 3, 2013 4:30 AM by Frank Kulash RSS

    Requete sur les dates

    1000638
      Bonjour,

      en sql ou plsql je recupere d'un requete une date, du style select date from toto;

      je veux calculer cette meme date de l'annee -1 mais qu'elle corresponde au meme jour

      est ce possible facilement
      merci
        • 1. Re: Requete sur les dates
          Frank Kulash
          Hi,

          Welcome to the forum. (Pardon, je ne parle pas bien le francais.)
          user4531526 wrote:
          Bonjour,

          en sql ou plsql je recupere d'un requete une date, du style select date from toto;
          DATE is not a good column name. Use something like DT or ENTRY_DATE instead.
          je veux calculer cette meme date de l'annee -1 mais qu'elle corresponde au meme jour

          est ce possible facilement
          merci
          How about:
          SELECT  ADD_MONTHS (dt, -12)  AS dt_minus_1_year
          ,       dt
          FROM    toto;
          If dt is the last day of February (whether that is February 28 or 29), then dt_minus_1_year will be the last day of February (whether that is February 28 or 29).
          In all other cases, the month and day of dt_minus_1_year will be the same as the month and day of dt, regardless of leap years.
          • 2. Re: Requete sur les dates
            BrendanP
            Ok, je ne peux résister à la tentation de pratiquer mon français - corrigez à volonté:
            SELECT Add_Months (Trunc (SYSDATE),-12), Trunc(SYSDATE)-365 FROM DUAL;
            'Add_Months' gérera les années bissextiles
            • 3. Re: Requete sur les dates
              Manik
              Trunc (sysdate) -365 peut être trompeur, n'oubliez pas les années bissextiles

              Add_Months (Trunc (SYSDATE),-12) corriger.

              Cheers,
              Manik.
              • 4. Re: Requete sur les dates
                1000638
                oui merci mais imaginez si la date est un mercredi, comment puis je trouver le mercredi de l'année présédente la plus proche
                d'avance merci
                • 5. Re: Requete sur les dates
                  jeneesh
                  Hello,

                  This is an International forum. It is not considered as a good etiquette to post in your local language.

                  Thanks.
                  • 6. Re: Requete sur les dates
                    1000638
                    yes thank you but imagine if the date is a Wednesday, how do I find the year on Wednesday nearest
                    thank you in advance
                    • 7. Re: Requete sur les dates
                      jeneesh
                      user4531526 wrote:
                      yes thank you but imagine if the date is a Wednesday, how do I find the year on Wednesday nearest
                      thank you in advance
                      Do you want to find the nearest date afterbefore one year with the same DAY as DT..?
                      next_day(ADD_MONTHS (dt, -12)-7,to_char(dt,'fmday'))
                      Edited by: jeneesh on Apr 3, 2013 12:35 PM
                      • 8. Re: Requete sur les dates
                        1000638
                        very good thanks you!
                        • 9. Re: Requete sur les dates
                          1000638
                          select trunc(next_day(ADD_MONTHS (to_date('02/01/2010'), -12))-7,to_char(to_date('02/01/2010'),'fmday')) from dual;

                          if i do a date how to do
                          • 10. Re: Requete sur les dates
                            1000638
                            select trunc(next_day(ADD_MONTHS (to_date('02/01/2010'), -12))-7,to_char(to_date('02/01/2010'),'fmday')) from dual;

                            with a date how to do?
                            • 11. Re: Requete sur les dates
                              jeneesh
                              When you use TO_DATE, you have to pass the format
                              select next_day
                                     (
                                     ADD_MONTHS 
                                       (
                                        to_date('02/01/2010','dd/mm/yyyy')), -12 --"or mm/dd/yyyy ?
                                          )-7,to_char(to_date('02/01/2010','dd/mm/yyyy'),'fmday')
                                      )  dt
                              from dual;
                              {code}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
                              • 12. Re: Requete sur les dates
                                1000638
                                select next_day
                                (
                                ADD_MONTHS
                                (
                                to_date('02/01/2010','dd/mm/yyyy'), -12 --"or mm/dd/yyyy ?
                                )-7,to_char(to_date('02/01/2010','dd/mm/yyyy'),'fmday')) dt
                                from dual;

                                ok it's this thks
                                • 13. Re: Requete sur les dates
                                  Frank Kulash
                                  Hi,
                                  user4531526 wrote:
                                  yes thank you but imagine if the date is a Wednesday, how do I find the year on Wednesday nearest
                                  thank you in advance
                                  So, given a DATE such as Wednesday, April 3, 2013, you want to find the closest Wednesday to April 3, 2012?
                                  That's
                                  SELECT  dt - 364
                                  FROM     table_x;
                                  Since every year has either 365 or 366 days, ADD_MONTHS (dt, -12) will always return DATE that is either 1 or 2 days over 52 weeks. Either way, the DATE that is exactly 52 weeks (= 52 * 7 = 364 days) earlier will be the closest.