1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Need help in dates query

Discussion in 'SQL PL/SQL' started by jagadekara, Apr 24, 2014.

  1. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi All,

    I need one query to display a date of 01-May-year.
    I am giving input as date. So requirement is when I gave one date I need 01-May-Past Year.

    If I give 20-Jan-2012 I need to display as 01-May-2011
    If I give 20-May-2012 I need to display 01-May-2012


    So please help me....
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.
    Code (SQL):


    SELECT
       dd,
       add_months(trunc(dd,'yyyy'),decode(EXTRACT(MONTH FROM dd),1,-8,4)) nd
    FROM (SELECT to_date ('20-JAN-2012','DD-MON-YYYY') dd FROM dual UNION ALL
          SELECT to_date ('20-MAY-2012','DD-MON-YYYY') dd FROM dual UNION ALL
          SELECT to_date ('20-JUN-2012','DD-MON-YYYY') dd FROM dual      
          );

    SQL>

    DD          ND
    ----------- -----------
    20.01.2012  01.05.2011
    20.05.2012  01.05.2012
    20.06.2012  01.05.2012

     
     
    jagadekara likes this.
  3. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Thanks a lot Sergey Krasnoslobodtsev.
     
  4. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi,

    If I give 20-Jan-2012 I need to display as 01-May-2011
    If I give 20-May-2012 I need to display 01-May-2012

    It is working fine, But

    If I give 20-Feb-2012 I need to display as 01-May-2011
    but it is displaying 01-may-2012

    also

    If I give 20-Mar-2012 I need to display as 01-May-2011
    but it is displaying 01-may-2012
     
  5. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    specify, what boundaries for transition on a year ago?

    try this....

    Code (SQL):


    SELECT
       dd,
       add_months(trunc(dd,'yyyy'),CASE WHEN EXTRACT(MONTH FROM dd) < 4 THEN -8 ELSE 4 END) nd
    FROM (SELECT to_date ('20-JAN-2012','DD-MON-YYYY') dd FROM dual UNION ALL
          SELECT to_date ('20-MAY-2012','DD-MON-YYYY') dd FROM dual UNION ALL
          SELECT to_date ('20-FEB-2012','DD-MON-YYYY') dd FROM dual      UNION ALL
    SELECT to_date ('20-MAR-2012','DD-MON-YYYY') dd FROM dual
          );
     
     
  6. raavi

    raavi Active Member

    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    75
    Try this...

    SELECT '1-May-'||to_char(EXTRACT(YEAR FROM SYSDATE)-1) FROM DUAL
     
  7. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi Raavi,

    Your query returns previous year...

    SELECT '1-May-'||to_char(EXTRACT(YEAR FROM to_date('02-June-2012'))-1) FROM DUAL

    1-May-2011

    But I Need

    1-May-2012

    Any How thanks for reply... I already got solution for this.

    Here is the solution....

    SELECT
    dd,
    add_months(trunc(dd,'yyyy'),CASE WHEN EXTRACT(MONTH FROM dd) < 5 THEN -8 ELSE 4 END) nd
    FROM (SELECT to_date :)P_DATE,'DD-MON-YYYY') dd FROM dual );
     
  8. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi,

    The following SQL may also be used:

    Code (SQL):
    SELECT ADD_MONTHS( TRUNC( ADD_MONTHS( TO_DATE (:P_DATE,'DD-MON-YYYY'), -4), 'Y'), 4)
    FROM dual;
    Regards,
    Rajen.
     
    jagadekara likes this.
  9. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Thanks Rajen,

    You gave a simple code than mine.
    Thanks A lot.
     
    rajenb likes this.