Results 1 to 4 of 4

Thread: dates

  1. #1
    Join Date
    Jul 2005
    Posts
    276

    Unanswered: dates

    I need to query on accounting_period(month) and fiscal_year(year) for the past 3 months
    where both of them are number fields. I need to get all the records for the past 3 months. Can someone please help me with this.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Sample data would surely help; now I had to build my own test case (and I'm not sure it is correct). Shame on you! Next time provide it by yourself.
    Code:
    CREATE TABLE fiscal (acc_period NUMBER, fiscal_year NUMBER, value NUMBER);
    
    INSERT ALL
      INTO fiscal VALUES (11, 2006, 300)
      INTO fiscal VALUES (2, 2007, 100)
      INTO fiscal VALUES (4, 2007, 200)
      INTO fiscal VALUES (5, 2007, 400)
    SELECT * FROM dual;
    
    SELECT * FROM fiscal
      WHERE TO_DATE(LPAD(TO_CHAR(acc_period), 2, '0') || fiscal_year, 'mmyyyy') >=
            ADD_MONTHS(SYSDATE, -3);
    
    ACC_PERIOD FISCAL_YEAR      VALUE
    ---------- ----------- ----------
             4        2007        200
             5        2007        400

  3. #3
    Join Date
    Jul 2005
    Posts
    276
    I'm very sorry for that.
    The sample data what you created is right, but my tables have strange values for accounting_period(243,112,0) what I have to take care of for now. Perhaps they will rectify the defect later on.
    When I run the query it gives me "not a valid month" error.

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    This is what happens when people expect us to be fortune tellers; how are we supposed to know that a MONTH column contains something like (243,112,0)?!? Obviously, it is "not a valid month".

Posting Permissions

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