Why is there a discrepancy between ActiveRecord SQL and PL/SQL Developer SQL?

Question

I have a select statement in Oracle PL/SQL Developer that's retrieving values based on a date:

select * from <table> where to_date(create_date) = to_date('20090506', 'YYYYMMDD')

where create_date is of Oracle type date. This returns a non-empty set as it should. However, in ActiveRecord:

<Table>.find_by_sql("select * from <table> where to_date(create_date) = to_date('20090506', 'YYYYMMDD')")

returns no rows, which is incorrect. I suspect it has something to do with how ActiveRecord handles Time/Date/DateTime objects.

Any ideas? Thanks

Answer 1

Well this is my hack. I'm not proud of it but it'll work. Any better answers that actually stick to the Ruby/Oracle Date types would be better in my opinion.

Table.find_by_sql("
  select * from <table>
  where to_char(create_date, 'YYYY-MM-DD') = '2009-05-06'
")

Answer 2

Given your own 'hack' and the column name create-DATE, I suspect create-date to be a DATE column. Oracle is 'friendly' enough to not raise an error when you do to_date of a date column, but it would be better if it did raise an error. So the correct solution would be (both in ORACLE! as in ActiveRecord):

select * from <table> where create_date = to_date('20090506', 'YYYYMMDD')

In short: Don't use to_date() on a date column.