Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2004
    Posts
    3

    Unanswered: SQL Question with Dates

    Hi, I have got a table where the month and year are separated into two different columns. Example:

    Month Year ID
    3 2005 123
    2 2005 123
    1 2005 123
    12 2004 123
    11 2004 123
    10 2004 123
    9 2004 123
    8 2004 123
    7 2004 123
    6 2004 123
    5 2004 123
    4 2004 123
    .
    .
    .
    and so on...

    I am passing in the last entry for month and year (3, 2005) in my WHERE clause and I want to retrieve all the entries within the past year.. (so returns the 12 records above from 4/2004 to 3/2005).

    How can I do this? Note that the fields in that table are not of DATE type.

    Thanks!

  2. #2
    Join Date
    Jan 2004
    Location
    UK
    Posts
    52
    I think this will help u,

    select to_date('01/'||ltrim(rtrim(to_char(month)))||'/'||ltrim(rtrim(to_char(year))),'dd/mm/yyyy')
    from datetab where to_date('01/'||ltrim(rtrim(to_char(month)))||'/'||ltrim(rtrim(to_char(year))),'dd/mm/yyyy')
    between to_date('07/2004','MM/YYYY') and to_date('02/2005','MM/YYYY')
    /

    and it give the following result:
    TO_DATE('
    ---------
    01-JUL-04
    01-AUG-04
    01-SEP-04
    01-OCT-04
    01-NOV-04
    01-DEC-04
    01-JAN-05
    01-FEB-05

    now u you can fine tune it as per ur requitement...

    good luck,
    Gautam Paul
    Last edited by gpeee; 04-30-04 at 05:51.

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Or:
    Code:
    SQL> select *
      2  from datetab
      3  where year*100+month between 200404 and 200503;
    
         MONTH       YEAR
    ---------- ----------
             1       2005
             2       2005
             3       2005
             4       2004
             5       2004
             6       2004
             7       2004
             8       2004
             9       2004
            10       2004
            11       2004
            12       2004
    If you wanted to base the criteria on the current date somehow you could do something like this:
    Code:
    SQL> select *
      2  from datetab
      3  where to_char(year*100+month) between to_char(add_months(sysdate,-11),'YYYYMM')
      4                                    and to_char(sysdate,'YYYYMM')
      5  order by year, month;
    
         MONTH       YEAR
    ---------- ----------
             5       2003
             6       2003
             7       2003
             8       2003
             9       2003
            10       2003
            11       2003
            12       2003
             1       2004
             2       2004
             3       2004
             4       2004
    
    12 rows selected.

Posting Permissions

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