Results 1 to 5 of 5
  1. #1
    Join Date
    May 2004
    Posts
    14

    Unanswered: SQL query to get three month old record

    Hi,

    I have a table TRANSFER(ID, TIME) with type Number and Timestamp respectively.
    Now I want to write a query which gets me the transfers done in past three months.

    Any help will be appreciated.

    Thanks

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    Hi,

    firstly you should define, what exact interval does the "past three months" represent.
    Is it from June, 1st til August, 31st?
    Is it from June, 23rd til September, 22nd?
    Does it include hours/minutes from the current date?

    Anyway, you may use ADD_MONTHS, TRUNC and SYSDATE functions in WHERE condition. Have a look at them into SQL Reference book. It is available with other Oracle documentation books e.g. online on http://tahiti.oracle.com/

  3. #3
    Join Date
    May 2004
    Posts
    14
    Three month mean between today and past three month.
    I am looking for something like
    SELECT id FROM transfer
    WHERE DATE BETWEEN TODAY() AND <some_function_to_find_three_month>

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Anyway, you may use ADD_MONTHS, TRUNC and SYSDATE functions in WHERE condition. Have a look at them into SQL Reference book. It is available with other Oracle documentation books e.g. online on Search and Download Oracle Database, Application Server, and Collaboration Suite Documentation

    >I have a table TRANSFER(ID, TIME)
    >WHERE DATE BETWEEN
    You need to be consistent with column names.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by unzip View Post
    Three month mean between today and past three month.
    I am looking for something like
    SELECT id FROM transfer
    WHERE DATE BETWEEN TODAY() AND <some_function_to_find_three_month>
    Be aware that TIMESTAMP data type holds date and time to nanoseconds (9 decimal places). What day and time shall be exactly "past three month" now?
    In Oracle, I already stated the functions:
    TODAY() = SYSDATE
    adding/subtracting months from date = ADD_MONTHS
    And, the lower bound shall be stated first, so you should probably swap values in BETWEEN:
    BETWEEN "some date/time before 3 months" AND "today with/without time"

Tags for this Thread

Posting Permissions

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