Results 1 to 6 of 6

Thread: accounting_dt

  1. #1
    Join Date
    Jul 2005
    Posts
    276

    Unanswered: accounting_dt

    Can someone please help me with this query.

    I want to get just the current month and the previous month records.

    If I give

    where
    accounting_dt>select add_months(sysdate,-2) from dual;

    gives the last 2 months data which is last 60days.

    What if I want the just the current month and the previous month?

  2. #2
    Join Date
    Nov 2006
    Location
    Indianapolis
    Posts
    90
    try add_months(trunc(sysdate,'MM'),-2)

  3. #3
    Join Date
    Jul 2005
    Posts
    276
    Great!! Thanks so much Indy_tomcat.

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Also , do not do the select to dual. use


    where
    accounting_dt > add_months(trunc(sysdate,'MM'),-2);
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Jul 2005
    Posts
    276
    It doesn't fetch any rows if I dont select from dual.

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    You must be kidding!
    Code:
    SQL> create table accounting (name varchar2(20), accounting_dt date);
    
    Table created.
    
    SQL> INSERT ALL
      2    INTO accounting VALUES ('Kekec', TO_DATE('05.01.2007', 'dd.mm.yyyy'))
      3    INTO accounting VALUES ('Mojca', TO_DATE('20.03.2007', 'dd.mm.yyyy'))
      4    INTO accounting VALUES ('Mikic', TO_DATE('30.04.2007', 'dd.mm.yyyy'))
      5    INTO accounting VALUES ('Tonka', TO_DATE('07.05.2007', 'dd.mm.yyyy'))
      6  SELECT * FROM dual;
    
    4 rows created.
    
    SQL> -- Look! No DUAL here!
    SQL> SELECT * FROM accounting
      2  WHERE accounting_dt > ADD_MONTHS(TRUNC(SYSDATE, 'mm'), -2)
      3  ORDER BY accounting_dt;
    
    NAME                 ACCOUNTI
    -------------------- --------
    Mojca                20.03.07
    Mikic                30.04.07
    Tonka                07.05.07
    
    SQL>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •