1

I don't know whats going on here, but here is my table (budget) with select * from budget :

|budget_id|as_of_date|quarter_1|quarter_2|quarter_3|quarter_4|
|1        |15-MAY-16 |400      |100      |100      |100

So say if I wanted to calculate sum for all quarters on the given date(15th May of 2016) or before I would use this query :

select as_of_dt from budget where as_of_dt <= to_date('2016-05-15', 'yyyy-MM-dd');

This query returns nothing:

select (quarter_1 + quarter_2 + quarter_3 + quarter_4) from budget where as_of_dt <= to_date('2016-05-15', 'yyyy-MM-dd');

However if I add one day to this :

select (quarter_1 + quarter_2 + quarter_3 + quarter_4) from budget where as_of_dt <= to_date('2016-05-16', 'yyyy-MM-dd');

I get the right result 700. I want to get all budget records before or equal to a given date string, why is the equal not working?

This might be a hint but I don't get it :

select to_date(as_of_dt, 'yyyy-MM-dd') from budget;
=> 16-MAY-15

select as_of_dt from budget;
=> 15-MAY-16

Somehow the year gets flipped or whatever is happening here, what do I do to get the right output with both equal and less than a given date ?

Improve this question
1
  • 1
    to_date converts a string to a date. Is AS_OF_DT a varchar or a DATE?. If it is DATE, then there is an implicit conversion of that to a VARCHAR before being given to to_date. In any event, to display the result of your to_date, there is an implicit conversion back to a string, using the controlling setting of NLS_DATE_FORMAT. I've written an article explaining in more detail at edstevensdba.com/oracle-data-types/…
    – EdStevens
    Commented May 20, 2016 at 22:36

1 Answer 1

Reset to default
1

The to_date function creates a date with a time. If I remember well this is 12:00. If the date in your table has a different time then this is also taken into account. When you want to test dates then you better put the trunc() function around your date like:

select as_of_dt
from   budget
where  trunc(as_of_dt) <= to_date('2016-05-15', 'yyyy-MM-dd');

Now the time part is the same in both dates.

Improve this answer
8
  • excellent, seems like it's working, is there any big performance penalty on truncating it and converting to date? is there a better way I can approach this?
    – Gandalf StormCrow
    Commented May 20, 2016 at 14:54
  • I never measured it but I think that it does not make a big difference unless the date is indexed. In that case the index will not be used and this can influence the performance.
    – Marco
    Commented May 20, 2016 at 14:55
  • 2
    If you expect to use indexes, this is huge performance penalty. Creating a function-based index is just unnecessary overhead. You could just go with as_of_dt < to_date('2016-05-16', 'yyyy-MM-dd'). And to_date creates the date with 0 hours, 0 minutes and 0 seconds when not specified.
    – Balazs Papp
    Commented May 21, 2016 at 9:45
  • @BalazsPapp whats a huge performance penalty? using trunc?
    – Gandalf StormCrow
    Commented May 25, 2016 at 13:41
  • 1
    Do as proposed by @BalazsPapp. Use the next day with the trunc: as_of_dt < to_date('2016-05-16', 'yyyy-MM-dd'). Now the index can be used. I say can because with the < it is very well possible that oracle decides not to use the index. You need to verify this with the explain plan function.
    – Marco
    Commented May 26, 2016 at 6:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.