Results 1 to 6 of 6

Thread: date ranges

  1. #1
    Join Date
    Mar 2007
    Posts
    45

    Unanswered: date ranges

    I am trying to read a date range, and the last date range in my data is 15-SEP-10.

    For some reason my code will not read the last range:

    Code:
    SELECT  t1.*  
    FROM csc_rdmp t1 WHERE  t1.rel_appr_date = TO_DATE('2010-07-01', 'YYYY-MM-DD')  
    AND  t1.rel_appr_date <= TO_DATE('2010-09-15', 'YYYY-MM-DD')
    How in the world do I get this to read a range of data?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    WHERE t1.rel_appr_date BETWEEN TO_DATE('2010-07-01', 'YYYY-MM-DD') AND TO_DATE('2010-09-15', 'YYYY-MM-DD')
    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.

  3. #3
    Join Date
    Mar 2007
    Posts
    45
    Well I tried these two and I can not get the date 15-SEP-10 to output

    Code:
    SELECT  t1.*  
    FROM csc_rdmp t1 WHERE  t1.rel_appr_date >= TO_DATE('2010-07-01', 'YYYY-MM-DD')  
    AND  t1.rel_appr_date <= TO_DATE('2010-09-15', 'YYYY-MM-DD'
    Code:
    SELECT  t1.*  
    FROM csc_rdmp t1 WHERE  t1.rel_appr_date BETWEEN TO_DATE('2010-07-01', 'YYYY-MM-DD')  
    AND  TO_DATE('2010-09-15', 'YYYY-MM-DD')

    But if I add a day to the query like so, I can get it to display, why? What do I need to do to handle this correctly so I always return the values?

    Code:
    FROM csc_rdmp t1 WHERE  t1.rel_appr_date  BETWEEN TO_DATE('2010-07-01', 'YYYY-MM-DD')  
    AND   TO_DATE('2010-09-15', 'YYYY-MM-DD') + 1

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    This is most probably because "rel_appr_date" column contains both DATE and TIME values. Here's an example, just to show you what I mean:
    Code:
    SQL> create table test (id number, rel_appr_date date);
    
    Table created.
    
    SQL> insert all
      2    into test (id, rel_appr_date) values (1, to_date('15.09.2010', 'dd.mm.yyyy'))
      3    into test (id, rel_appr_date) values (2, to_date('15.09.2010 05:30', 'dd.mm.yyyy hh24:mi'))
      4  select * from dual;
    
    2 rows created.
    
    SQL> alter session set nls_Date_format = 'dd.mm.yyyy hh24:mi';
    
    Session altered.
    
    SQL> select * from test;
    
            ID REL_APPR_DATE
    ---------- ----------------
             1 15.09.2010 00:00
             2 15.09.2010 05:30
    
    SQL> select * from test where rel_appr_date <= to_date('15.09.2010', 'dd.mm.yyyy');
    
            ID REL_APPR_DATE
    ---------- ----------------
             1 15.09.2010 00:00
    
    SQL> select * from test where rel_appr_date <= to_date('16.09.2010', 'dd.mm.yyyy');
    
            ID REL_APPR_DATE
    ---------- ----------------
             1 15.09.2010 00:00
             2 15.09.2010 05:30
    
    SQL>
    So, what happens when you simply SELECT values from a table, setting a correct NLS_DATE_FORMAT (so that you'd see both date and time)?

  5. #5
    Join Date
    Mar 2007
    Posts
    45
    ok so could'nt I use the trunc function and strip off the time and just use the date to compare to?

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    You could. But TRUNC would most probably cause your queries NOT to use indexes created on that column. Though, you could create a function-based index. Check the documentation (also, see what AskTom says about that feature).

Posting Permissions

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