# Rails and Oracle - can select date but cannot save

**URL:** <https://www.ruby-forum.com/t/rails-and-oracle-can-select-date-but-cannot-save/193202>\
**Category:** Rails\
**Created:** [August 13, 2010, 3:11am UTC](https://www.ruby-forum.com/t/rails-and-oracle-can-select-date-but-cannot-save/193202 "2010-08-13T03:11:51Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![techy\_bolek](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/ee59a6/32.png) [@techy\_bolek](https://www.ruby-forum.com/u/techy_bolek)\
**Post date:** [August 13, 2010, 3:11am UTC](https://www.ruby-forum.com/t/rails-and-oracle-can-select-date-but-cannot-save/193202/1 "2010-08-13T03:11:51Z")

</div>

I’m seeing a behavior in Rails that I find very strange. Having worked  
quite a bit with Oracle with other languages I would always dutifully do  
ALTER SESSION SET NLS\_DATE\_FORMAT = \<my\_date\_format\> and ensure I use  
that date format in my application.  
With Rails I still have not figured out how to execute an ALTER SESSION  
statement after connecting to the database (I’m using the the jdbc  
adapter). And sure enough I get a “date format not recognized” error  
when trying to save a value to a DATE column. But surprisingly when I  
select pre-inserted dates correctly.  
The default Rails date format in my platform is YYYY-MM-DD. The default  
date format of my Oracle database is DD-MMM-YY. Ultimately what I’m  
after is getting my dates to work both on select and save but I would  
welcome an explanation as to why Rails can select correctly with the  
application and the database date format being out of sync.

---

<div class="post-metadata">

**Author:** ![techy\_bolek](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/ee59a6/32.png) [@techy\_bolek](https://www.ruby-forum.com/u/techy_bolek)\
**Post date:** [August 13, 2010, 9:05pm UTC](https://www.ruby-forum.com/t/rails-and-oracle-can-select-date-but-cannot-save/193202/2 "2010-08-13T21:05:20Z")

</div>

Found how to set the date format for DB conversion on the Rails side as  
opposed to Oracle. It’s good but it’s not enough. I put the following in  
overrides.rb:

module ActiveSupport #:nodoc:  
module CoreExtensions #:nodoc:  
module Date #:nodoc:  
# Converting dates to formatted strings, times, and datetimes.  
module Conversions  
DATE\_FORMATS[:db] = “%d-%b-%y”  
end  
end  
end  
end

This gives me , for Aug 13th 2010, 13-Aug-10. However, Oracle expects  
12-AUG-10 (i.e. month all caps) and continues to reject the date on  
save. Needless to say select continues to work mysteriously.  
Need to figure out how to set NLS\_DATE\_FORMAT when the connection is  
established.

---

<div class="post-metadata">

**Author:** ![techy\_bolek](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/ee59a6/32.png) [@techy\_bolek](https://www.ruby-forum.com/u/techy_bolek)\
**Post date:** [August 13, 2010, 9:20pm UTC](https://www.ruby-forum.com/t/rails-and-oracle-can-select-date-but-cannot-save/193202/3 "2010-08-13T21:20:20Z")

</div>

On Fri, Aug 13, 2010 at 12:05 PM, Tomasz R.  
[removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> Found how to set the date format for DB conversion on the Rails side as  
> opposed to Oracle. It’s good but it’s not enough. I put the following in  
> overrides.rb:

> ```
> DATE_FORMATS[:db] = "%d-%b-%y"
> 
> ```

> This gives me , for Aug 13th 2010, 13-Aug-10. However, Oracle expects  
> 12-AUG-10 (i.e. month all caps) and continues to reject the date on  
> save.

DATE\_FORMATS[:db] = ‘%d-%b-%y’.upcase

Regardless, you might want to try the oracle\_enhanced adapter –  
[http://github.com/rsim/oracle-enhanced](http://github.com/rsim/oracle-enhanced)

## FWIW,

Hassan S. ------------------------ removed\_email\_address@domain.invalid  
twitter: @hassan

---

<div class="post-metadata">

**Author:** ![techy\_bolek](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/ee59a6/32.png) [@techy\_bolek](https://www.ruby-forum.com/u/techy_bolek)\
**Post date:** [August 14, 2010, 5:26pm UTC](https://www.ruby-forum.com/t/rails-and-oracle-can-select-date-but-cannot-save/193202/4 "2010-08-14T17:26:01Z")

</div>

On Sat, Aug 14, 2010 at 8:18 AM, Tomasz R.  
[removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> What’s the benefit of using Oracle enhanced over  
> activerecord-jdbc-adapter? Is is any faster/more reliable?

Less buggy – I had problems with the activerecord-jdbc-adapter that  
were fixed by switching to oracle\_enhanced. This was a while ago,  
and I don’t remember the details off the top of my head.

–  
Hassan S. ------------------------ removed\_email\_address@domain.invalid  
twitter: @hassan

---

<div class="post-metadata">

**Author:** ![techy\_bolek](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/ee59a6/32.png) [@techy\_bolek](https://www.ruby-forum.com/u/techy_bolek)\
**Post date:** [August 14, 2010, 5:18pm UTC](https://www.ruby-forum.com/t/rails-and-oracle-can-select-date-but-cannot-save/193202/5 "2010-08-14T17:18:41Z")

</div>

What’s the benefit of using Oracle enhanced over  
activerecord-jdbc-adapter? Is is any faster/more reliable?

I think I figured out the problem which imho is a bug in Oracle’s  
activerecord-jdbc-adapter. Through debugging I noticed that Rails  
converts the date using the Oracle’s TIMESTAMP function for example:

INSERT INTO \<table\_name\> VALUES (TIMESTAMP’2010-03-29’).

However Oracle expects TIMESTAMP to be ALWAYS invoked with the format  
‘YYYY-MM-DD H24:MI:SS’. That spells always, regardless of the  
NLS\_DATE\_FORMAT or NLS\_TIMESTAMP\_FORMAT settings. If you the time  
portion is not specified, the statement errors out with a “ORA-01861:  
literal does not match format string” message.  
Using the jdbc adapter from activerecord-jdbc-adapter 0.9.7 I found the  
problem to be with the JdbcSpec::Oracle::quoted\_date method:

module ::JdbcSpec

module Oracle

```
def quoted_date(value)
    %Q{TIMESTAMP'#{super}'}
  end
end

```

end  
end

The method first converts the value to the db format as specified by:

ActiveSupport::CoreExtensions:📅:Conversions::DATE\_FORMATS[:db]  
which is pre-set to Y-%m-%d".

which gets the date portion right will NEVER work with TIMESTAMP since  
the time portion is missing. For zero times the DATE method should be  
used instead. With the following fix everything works like a charm:

module ::JdbcSpec

module Oracle

```
def quoted_date(value)
  if value.acts_like?(:time)
    %Q{TIMESTAMP'#{super}'}
  else
    %Q{DATE'#{super}'}
  end
end

```

end  
end