This discussion is archived
12 Replies Latest reply: Sep 10, 2013 2:04 PM by rp0428 RSS

Best Practice Question for Date Compares

CSchrieber Newbie
Currently Being Moderated

Hello,

 

I'm far from an expert on Oracle and PL/SQL, so I may have this all wrong.  I'm working on some selection scripts, and the guy that wrote these before me likes to convert parts of dates to numbers in order to do a compare.  It seems to me a trunc(date, part) would be much more efficient.  Can anyone confirm my thoughts here?

 

I'm seeing this a lot:

 

AND    TO_NUMBER (TO_CHAR (scheduleddate, 'YYYY')) = TO_NUMBER (TO_CHAR (SYSDATE - 1, 'YYYY'))

 

Seems to me that I'd be better off just doing:

 

trunc(scheduleddate, 'YEAR') = trunc(SysDate, 'YEAR').

 

 

Does that make sense?

 

Thanks!

 

Chad

  • 1. Re: Best Practice Question for Date Compares
    EdStevens Guru
    Currently Being Moderated

    CSchrieber wrote:

     

    Hello,

     

    I'm far from an expert on Oracle and PL/SQL, so I may have this all wrong.  I'm working on some selection scripts, and the guy that wrote these before me likes to convert parts of dates to numbers in order to do a compare.  It seems to me a trunc(date, part) would be much more efficient.  Can anyone confirm my thoughts here?

     

    I'm seeing this a lot:

     

    AND    TO_NUMBER (TO_CHAR (scheduleddate, 'YYYY')) = TO_NUMBER (TO_CHAR (SYSDATE - 1, 'YYYY'))

     

    Seems to me that I'd be better off just doing:

     

    trunc(scheduleddate, 'YEAR') = trunc(SysDate, 'YEAR').

     

     

    Does that make sense?

     

    Thanks!

     

    Chad

    I think you are smarter than the guy that wrote the original code.

    Go forth and teach others.

  • 2. Re: Best Practice Question for Date Compares
    SomeoneElse Guru
    Currently Being Moderated

    You might also consider something like this

     

    where scheduleddate >= trunc(SysDate, 'YEAR')

    and   scheduleddate <  add_months(trunc(SysDate, 'YEAR'), 12)

     

    This way you stand a better chance of using the index on scheduleddate (if there is one and it's appropriate).

  • 3. Re: Best Practice Question for Date Compares
    admin1 Newbie
    Currently Being Moderated

    For Year it may make sense, how about user only cares about month?

     

    To_CHAR('1-jan-2010', 'MM') = TO_CHAR('1-jan-2013','MM')

  • 4. Re: Best Practice Question for Date Compares
    CSchrieber Newbie
    Currently Being Moderated

    Thanks!  Question of clarification on your approach (in attempting to use the index).  The original query I showed (To_Number(To_Char.....) wouldn't use indexes either, would it?, So I shouldn't be losing anything with the change I suggest, correct?

  • 5. Re: Best Practice Question for Date Compares
    EdStevens Guru
    Currently Being Moderated

    admin1 wrote:

     

    For Year it may make sense, how about user only cares about month?

     

    To_CHAR('1-jan-2010', 'MM') = TO_CHAR('1-jan-2013','MM')

     

    did you try that?

    You are feeding character string ('1-jan-2010') to to_char ....

     

     

    SQL> SELECT TO_CHAR('1-jan-2010','MM') from dual;

    SELECT TO_CHAR('1-jan-2010','MM') from dual

                   *

    ERROR at line 1:

    ORA-01722: invalid number

  • 6. Re: Best Practice Question for Date Compares
    SomeoneElse Guru
    Currently Being Moderated

    Generally speaking (I won't say always), when you use a function on an indexed column you disable the use of the index.

     

    There are ways around it.  For example, you can create function-based indexes.

     

    But in your case, it's easier to code it in a way where you don't need a function on the indexed column.

  • 7. Re: Best Practice Question for Date Compares
    EdStevens Guru
    Currently Being Moderated

    CSchrieber wrote:

     

    Hello,

     

    I'm far from an expert on Oracle and PL/SQL, so I may have this all wrong.  I'm working on some selection scripts, and the guy that wrote these before me likes to convert parts of dates to numbers in order to do a compare.  It seems to me a trunc(date, part) would be much more efficient.  Can anyone confirm my thoughts here?

     

    I'm seeing this a lot:

     

    AND    TO_NUMBER (TO_CHAR (scheduleddate, 'YYYY')) = TO_NUMBER (TO_CHAR (SYSDATE - 1, 'YYYY'))

     

    Seems to me that I'd be better off just doing:

     

    trunc(scheduleddate, 'YEAR') = trunc(SysDate, 'YEAR').

     

     

    Does that make sense?

     

    Thanks!

     

    Chad

     

    however , you might want to play around with it a bit to confirm you get the behavior you want in your context.  I've not had much reason to use trunc, and in testing a few things just now to reply to admin1, I got some results that surprised me.  Another little learning project for me now.

     

    But I stand behind the essence of my original reply.  The guy that wrote your original code was jumping through too many hoops (transform functions).

  • 8. Re: Best Practice Question for Date Compares
    admin1 Newbie
    Currently Being Moderated

    I didn't. Sorry I simplified things a bit.

     

    My point is, if you rely on trunc(), you aren't flexible if it's not at the year level. If you to_char(somedate, 'MM') you get the month part, regardless of what year it is in. You can't do that using trunc().

  • 9. Re: Best Practice Question for Date Compares
    CSchrieber Newbie
    Currently Being Moderated

    Ed,

    I can do that.  I've been using it to answer the questions "Is the date this year" and "Is the date this month," so comparing TRUNC(SYSDATE, 'MONTH'/'YEAR') seem to get me what I want--shows first day of month or year, as I selected in the part.

     

    I've also been using it for "give me everything since the first day of last month--so,  MyDateField >= ADD_MONTHS(trunc(SysDate, 'MONTH'), -1) 

     

    Haven't seen anything that surprises me yet.

     

    Chad

  • 10. Re: Best Practice Question for Date Compares
    SomeoneElse Guru
    Currently Being Moderated

    > MyDateField >= ADD_MONTHS(trunc(SysDate, 'MONTH'), -1)

     

    This is fine.  And it gets the heart of your question.  The best practices are to keep date types as date types and not convert them to varchars or numbers or whatever.

     

    In this example, add_months and trunc work with dates and return dates.

  • 11. Re: Best Practice Question for Date Compares
    John Spencer Oracle ACE
    Currently Being Moderated

    admin1 wrote:

     

    I didn't. Sorry I simplified things a bit.

     

    My point is, if you rely on trunc(), you aren't flexible if it's not at the year level. If you to_char(somedate, 'MM') you get the month part, regardless of what year it is in. You can't do that using trunc().

    I don't know, trunc seems pretty flexible to me:

    SQL> select trunc(sysdate, 'year') year, trunc(sysdate, 'month') month,
      2         trunc(sysdate, 'day') day, trunc(sysdate, 'mi') minute
      3  from dual;

    YEAR                 MONTH                DAY                  MINUTE
    -------------------- -------------------- -------------------- --------------------
    01-jan-2013 00:00:00 01-sep-2013 00:00:00 08-sep-2013 00:00:00 10-sep-2013 16:55:00

    Which would make the OP's predicate something like:

    AND trunc(scheduleddate, 'year') = trunc(SYSDATE - 1, 'year')

    John

  • 12. Re: Best Practice Question for Date Compares
    rp0428 Guru
    Currently Being Moderated

    Which would make the OP's predicate something like:

    AND trunc(scheduleddate, 'year') = trunc(SYSDATE - 1, 'year')

    And would still preclude the use of any index on 'scheduleddate' other than a functional one; unlike a predicate with a date range.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points