Results 1 to 14 of 14
  1. #1
    Join Date
    Jul 2005
    Posts
    276

    Unanswered: Getting last months's records

    Hi All,

    I'm trying to retrieve the records for the last month and below is the query what I wrote-

    select * from tab1
    where date1 >= add_months(trunc(sysdate,'MM'),-1)
    and date1 <= last_day(to_date(sysdate,'dd/mm/yyyy'));

    Is there a better way to write this?

    -Nandinir

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Code:
    select * from tab1 
    where date1 between add_months(trunc(sysdate,'MM'),-1) 
    and trunc(sysdate,'MM') - (1/86400);
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Jul 2005
    Posts
    276
    Thank you very much Beilstwh!!

  4. #4
    Join Date
    Jul 2005
    Posts
    276
    I'm trying to retreive records from the first of last month to current date from a table where I have year and month columns.

    There is a chance to have M for month instead of MM. Any ideas?

    Thanks
    Nandinir

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I'm trying to retreive records from the first of last month to current date from a table where I have year and month columns
    Trying without any SQL might limit the results.

    Post DDL for tables.
    Post DML for test data.

    Post expected/desired results.
    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.

  6. #6
    Join Date
    Jul 2005
    Posts
    276
    Here you go-

    create table test(id number(5), year number(4), month number(2));

    insert into test values(11, 2009, 05);
    insert into test values(22, 2009, 06);
    insert into test values(33, 2009, 06);
    insert into test values(44, 2009, 07);
    insert into test values(55, 2009, 7);
    insert into test values(66, 2008, 12);
    insert into test values(77, 2009, 01);


    If I'm running it today, I expect to see all the records with year||month = 200906 and 200907.
    If I were running it in Jan 2009 then I would expect to see all records with year||month= 200812 and 200901

    SELECT ADD_MONTHS(TRUNC(SYSDATE,'MM'),-1) FROM dual; gives me the first of last month but not able to figure out how to fix the query to give me only the year and month as I have month and year columns in the table.

    SELECT TO_CHAR(SYSDATE,'yyyymm') FROM dual; gives me the current month and year.

    I would like to combine these 2 queries finally perhaps using between.

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    If TEST.MONTH is a NUMBER(2), it is impossible to store value of 05 or 06 in there. So what is it? A NUMBER, or a CHARACTER column?

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Some of the challenge results from the fact that by storing Month & Year as numbers & in different columns, they can not directly be treated as DATE.
    The table "design" has made the answer more complicated than it needs to be.

    I'd create/convert everything to DATE datatype using TO_DATE() & then
    SELECT .... FROM TEST
    WHERE <TABLE_DATE> BETWEEN <LAST_MONTH> AND <THIS_MONTH>
    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.

  9. #9
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Some of the challenge results from the fact that by storing Month & Year as numbers & in different columns, they can not directly be treated as DATE.
    The table "design" has made the answer more complicated than it needs to be.

    I'd create/convert everything to DATE datatype using TO_DATE() & then
    SELECT .... FROM TEST
    WHERE <TABLE_DATE> BETWEEN <LAST_MONTH> AND <THIS_MONTH>

    Alternatively
    CREATE VIEW BETTER_TEST

    that contains ID & DATE (built from Month & Year)
    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.

  10. #10
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Hm, OK, if "month" can be M or MM, I guess that this column is of a character datatype. If so, would such a query help?
    Code:
    SQL> SELECT * FROM TEST
      2  WHERE LPAD(MONTH, 2, '0') || YEAR IN
      3    (TO_CHAR(TO_DATE('&&today', 'dd.mm.yyyy'), 'mmyyyy'),
      4     TO_CHAR(ADD_MONTHS(TO_DATE('&&today', 'dd.mm.yyyy'), -1), 'mmyyyy')
      5    );
    
            ID       YEAR MO
    ---------- ---------- --
            66       2008 12
            77       2009 01
    
    SQL> undefine today;
    SQL> /
    Enter value for today: 08.07.2009
    
            ID       YEAR MO
    ---------- ---------- --
            22       2009 06
            33       2009 06
            44       2009 07
            55       2009 7
    
    SQL>

  11. #11
    Join Date
    Jul 2005
    Posts
    276
    Littlefoot - Your solution works perfect and I get the desired results too. But can I pass in the sysdate instead of the date parameter?

  12. #12
    Join Date
    Dec 2008
    Posts
    99
    Quote Originally Posted by nandinir
    Hi All,

    I'm trying to retrieve the records for the last month and below is the query what I wrote-

    select * from tab1
    where date1 >= add_months(trunc(sysdate,'MM'),-1)
    and date1 <= last_day(to_date(sysdate,'dd/mm/yyyy'));

    Is there a better way to write this?

    -Nandinir

    Hello
    This should be enough

    select * from tab1
    where trunc(date1) =trunc( add_months((sysdate,'MM'),-1) )

    Best Regards

  13. #13
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Code:
    select * from my_table
    where to_date(lpad(month,2,'0')||year,'mmyyyy') between 
    add_months(trunc(sysdate,'MM'),-1) and sysdate;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  14. #14
    Join Date
    Jul 2005
    Posts
    276
    Perhaps i should have started a new thread. I continued the same thread cause i had a similar question.
    Previously it was only last months records and this time I was asking for last month and this month's records.

    Thanks again guys!

Posting Permissions

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