Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2004
    Posts
    51

    Unanswered: Past three years data

    Hi
    Is it possible to get the last 3 years data in Oracle
    I have one date column in the table and all I want is to get all the records for the last 3 years
    How can I achieve this?

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I'd use SELECT statement. And you?

    Just kidding!

    What does it mean "last 3 years"? Is it "today - 3 years", or (as it is 2007 now) all records since 2004?

    Whichever it is, you'll have to subtract something from something.

    First case could be resolved using
    Code:
    SELECT records
    FROM this_table
    WHERE date_column >= ADD_MONTHS(SYSDATE, -12*3);
    while another one might get a result using
    Code:
    SELECT records
    FROM this_table
    WHERE date_colum <= TRUNC(ADD_MONTHS(SYSDATE, -3*12), 'YYYY');

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    You have to have a date column which specifies the record's age. There's no other way to determine how old a record is.
    -=cf

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    That's exactly what he said.
    Quote Originally Posted by superprogrammer
    I have one date column in the table ...

  5. #5
    Join Date
    Mar 2007
    Posts
    37
    WHERE date_column >= ADD_MONTHS(SYSDATE, -12*3);
    the first solution provided by littlefoot has a slight typing mistake!

    the comparison operator would be <=


  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Nope. It really should be >=. Translated to English, "show me all records where date column has values (today - 36 months) and newer."

    But, heh, the second query should also be >=. Shouldn't it?

    Thank you for reading my attempts and detecting errors in them!
    Last edited by Littlefoot; 03-31-07 at 15:31.

  7. #7
    Join Date
    Mar 2007
    Posts
    37
    yeah, my mistake mate!

Posting Permissions

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