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 on Period Query

Discussion in 'SQL PL/SQL' started by jagadekara, Sep 15, 2014.

  1. jagadekara

    jagadekara Forum Guru

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

    I have a table like this...

    create table xx_fa_trxn_history_trx_v(period varchar2(100));

    SELECT * FROM xx_fa_trxn_history_trx_v;

    PERIOD
    --------
    APR-12-13
    MAY-12-13
    JUN-12-13
    JUL-12-13
    JUL-13-14
    APR-13-14
    MAY-13-14

    select *
    from xx_fa_trxn_history_trx_v
    where 1=1
    and period between :p_PERIOD1 --APR-12-13
    and :p_PERIOD2; -JUN-12-13

    PERIOD
    -----------
    APR-12-13
    JUN-12-13
    JUL-12-13
    JUL-13-14
    APR-13-14

    I gave APR-12-13 and JUN-12-13 but I got APR-13-14 and not getting MAY-12-13.

    It consider as character. So what can I do for this to consider as date and should display below results for this query.

    PERIOD
    ----------
    APR-12-13
    MAY-12-13
    JUN-12-13

    Please help me.
     
  2. neelambhumij

    neelambhumij Active Member

    Messages:
    51
    Likes Received:
    2
    Trophy Points:
    160
    Location:
    JOHANNESBURG
    Hi Jagadekara

    Hope this will help.

    select * from
    xx_fa_trxn_history_trx_v ab
    where 1=1
    AND TO_DATE(period,'MON-DD-YY') BETWEEN TO_DATE:)P_PERIOD1,'MON-DD-YY') AND TO_DATE:)P_PERIOD2,'MON-DD-YY')

    Thanks and Regards
    Neelam
     
  3. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Thank you so much Neelam...
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    If you want to 'consider them as dates' you make them dates, it's that simple:


    Code (SQL):

    SQL> --
    SQL> -- Be silly and create a column containing dates as a character type
    SQL> --
    SQL> CREATE TABLE xx_fa_trxn_history_trx_v(period varchar2(100));


    TABLE created.


    SQL>
    SQL> --
    SQL> -- Load the 'date' data
    SQL> --
    SQL> INSERT ALL
      2  INTO xx_fa_trxn_history_trx_v
      3  VALUES('APR-12-13')
      4  INTO xx_fa_trxn_history_trx_v
      5  VALUES('MAY-12-13')
      6  INTO xx_fa_trxn_history_trx_v
      7  VALUES('JUN-12-13')
      8  INTO xx_fa_trxn_history_trx_v
      9  VALUES('JUL-12-13')
     10  INTO xx_fa_trxn_history_trx_v
     11  VALUES('JUL-13-14')
     12  INTO xx_fa_trxn_history_trx_v
     13  VALUES('APR-13-14')
     14  INTO xx_fa_trxn_history_trx_v
     15  VALUES('MAY-13-14')
     16  SELECT * FROM dual;


    7 ROWS created.


    SQL>
    SQL> commit;


    Commit complete.


    SQL>
    SQL> --
    SQL> -- Create variables
    SQL> --
    SQL> variable P_PERIOD1 varchar2(12)
    SQL> variable P_PERIOD2 varchar2(12)
    SQL>
    SQL> BEGIN
      2          :P_PERIOD1:='APR-12-13';
      3          :P_PERIOD2:='JUN-12-13';
      4  END;
      5  /


    PL/SQL PROCEDURE successfully completed.


    SQL>
    SQL> --
    SQL> -- Now try to query as if they were dates
    SQL> --
    SQL>  SELECT *
      2   FROM xx_fa_trxn_history_trx_v
      3   WHERE 1=1  -- useless
      4   AND period BETWEEN :P_PERIOD1 --APR-12-13
      5   AND :P_PERIOD2 --JUN-12-13
      6  /


    PERIOD
    --------------------------------------------------------------------------------
    APR-12-13
    JUN-12-13
    JUL-12-13
    JUL-13-14
    APR-13-14


    SQL>
    SQL> --
    SQL> -- Doesn't work
    SQL> --
    SQL> -- Re-create the table with the proper data type
    SQL> --
    SQL> DROP TABLE xx_fa_trxn_history_trx_v purge;


    TABLE dropped.


    SQL> CREATE TABLE xx_fa_trxn_history_trx_v(period DATE);


    TABLE created.


    SQL>
    SQL> --
    SQL> -- Load the 'date' data
    SQL> --
    SQL> INSERT ALL
      2  INTO xx_fa_trxn_history_trx_v
      3  VALUES(to_date('APR-12-13', 'MON-DD-RR'))
      4  INTO xx_fa_trxn_history_trx_v
      5  VALUES(to_date('MAY-12-13', 'MON-DD-RR'))
      6  INTO xx_fa_trxn_history_trx_v
      7  VALUES(to_date('JUN-12-13', 'MON-DD-RR'))
      8  INTO xx_fa_trxn_history_trx_v
      9  VALUES(to_date('JUL-12-13', 'MON-DD-RR'))
     10  INTO xx_fa_trxn_history_trx_v
     11  VALUES(to_date('JUL-12-14', 'MON-DD-RR'))
     12  INTO xx_fa_trxn_history_trx_v
     13  VALUES(to_date('APR-13-14', 'MON-DD-RR'))
     14  INTO xx_fa_trxn_history_trx_v
     15  VALUES(to_date('MAY-13-14', 'MON-DD-RR'))
     16  SELECT * FROM dual;


    7 ROWS created.


    SQL>
    SQL> commit;


    Commit complete.


    SQL>
    SQL> --
    SQL> -- Populate variables
    SQL> --
    SQL> BEGIN
      2          :P_PERIOD1:=to_date('APR-12-13','MON-DD-RR');
      3          :P_PERIOD2:=to_date('JUN-12-13','MON-DD-RR');
      4  END;
      5  /


    PL/SQL PROCEDURE successfully completed.


    SQL>
    SQL> --
    SQL> -- Now query the dates
    SQL> --
    SQL>  SELECT *
      2   FROM xx_fa_trxn_history_trx_v
      3   WHERE 1=1  -- useless
      4   AND period BETWEEN :P_PERIOD1 --APR-12-13
      5   AND :P_PERIOD2 --JUN-12-13
      6  /


    PERIOD
    ---------
    12-APR-13
    12-MAY-13
    12-JUN-13


    SQL>

     
     
  5. jagadekara

    jagadekara Forum Guru

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

    It's not date it's a period.

    I don't want to change the data type of table.
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Periods are defined by dates, it's that simple.