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 ?

  • 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 May 20 '16 at 22:36
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.

  • 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 May 20 '16 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 May 20 '16 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 May 21 '16 at 9:45
  • @BalazsPapp whats a huge performance penalty? using trunc? – Gandalf StormCrow May 25 '16 at 13:41
  • When you use a function like trunc with a field that is in an index then the optimizer does not use the index but will do a full table scan. This means that all the rows will be read. This can take more time then when it uses the index. This all depends on how many rows there are in your table and how many rows are expected to be returned. – Marco May 25 '16 at 17:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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