This discussion is archived
13 Replies Latest reply: Apr 3, 2013 2:30 AM by Frank Kulash RSS

Requete sur les dates

1000638 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    very good thanks you!
  • 9. Re: Requete sur les dates
    1000638 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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.

Legend

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