Have Geek Brain

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

I have a select statement in Oracle PL / SQL Developer that retrieves values ​​based on a date:

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

where create_date

has a date of type Oracle. 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 strings, which is not correct. I suspect it has something to do with how ActiveRecord handles Time / Date / DateTime objects.

Any ideas? Thanks to

0


a source to share


2 answers


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

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


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

+2


a source


Ok, this is my hack. I'm not proud of it, but it will work. Any better answers that actually stick with 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'
")
0


a source






More articles:


All Articles