# Oracle date problems, and how to make "alter session" work

**URL:** <https://www.ruby-forum.com/t/oracle-date-problems-and-how-to-make-alter-session-work/157301>\
**Category:** Rails\
**Created:** [January 9, 2009, 9:05am UTC](https://www.ruby-forum.com/t/oracle-date-problems-and-how-to-make-alter-session-work/157301 "2009-01-09T09:05:06Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![michael\_teter](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/e47c2d/32.png) [@michael\_teter](https://www.ruby-forum.com/u/michael_teter)\
**Post date:** [January 9, 2009, 9:05am UTC](https://www.ruby-forum.com/t/oracle-date-problems-and-how-to-make-alter-session-work/157301/1 "2009-01-09T09:05:06Z")

</div>

Howdy.

Something change recently (Oracle upgrade and/or new gem) that’s  
causing ActiveRecord to give me very unfriendly dates from Oracle.

I believe the old dates I was getting were “YYYY-MM-DD”. The ones I’m  
getting now are like this:  
Tue Dec 23 00:00:00 -0600 2008

Oddly enough, telling my ActiveRecord subclass to fetch “select to\_char  
(thedate,‘YYYY-MM-DD’) as thedate” is not working, as I’m still  
receiving the very long date listed above.

I found reference to this somewhere online: ALTER SESSION SET  
NLS\_DATE\_FORMAT = ‘YYYY-MM-DD’

I have executed that in my app, but subsequent queries are still  
returning the long date.

Suggestions?

---

<div class="post-metadata">

**Author:** ![michael\_teter](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/e47c2d/32.png) [@michael\_teter](https://www.ruby-forum.com/u/michael_teter)\
**Post date:** [January 9, 2009, 9:23am UTC](https://www.ruby-forum.com/t/oracle-date-problems-and-how-to-make-alter-session-work/157301/2 "2009-01-09T09:23:18Z")

</div>

you could try putting the following in your environment.rb:

ActiveSupport::CoreExtensions:📅:Conversions::DATE\_FORMATS.update  
(:default =\> ‘%Y-%m-%d’)

---

<div class="post-metadata">

**Author:** ![michael\_teter](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/e47c2d/32.png) [@michael\_teter](https://www.ruby-forum.com/u/michael_teter)\
**Post date:** [January 9, 2009, 9:53am UTC](https://www.ruby-forum.com/t/oracle-date-problems-and-how-to-make-alter-session-work/157301/3 "2009-01-09T09:53:11Z")

</div>

Ok, I’ve done this, and it works for me mostly…

Now I have to figure out why to\_xml() is changing the date format to  
the big ugly version .

For example:

> > errs = Errors.find(:all, :conditions =\> “errtype = 2”)  
> > =\> [#\<Errors blah blah blah, crtd: “2009-01-07 00:00:00” …]

> > errs.to\_xml(:dasherize =\> false, :skip\_types =\> true)  
> > =\> "\<?xml version=\"1.0\" encoding=\"UTF-8\"?\>\n\n   
> > \n Wed Jan 07 00:00:00 -0600 2009 …]

So my problem is in to\_xml…

---

<div class="post-metadata">

**Author:** ![michael\_teter](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/e47c2d/32.png) [@michael\_teter](https://www.ruby-forum.com/u/michael_teter)\
**Post date:** [January 9, 2009, 9:57am UTC](https://www.ruby-forum.com/t/oracle-date-problems-and-how-to-make-alter-session-work/157301/4 "2009-01-09T09:57:18Z")

</div>

a guy in this thread had the same problem and obviously fixed it  
(scroll down to the end).  
[http://www.ruby-forum.com/topic/57923](http://www.ruby-forum.com/topic/57923)

---

<div class="post-metadata">

**Author:** ![michael\_teter](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/e47c2d/32.png) [@michael\_teter](https://www.ruby-forum.com/u/michael_teter)\
**Post date:** [January 9, 2009, 10:07am UTC](https://www.ruby-forum.com/t/oracle-date-problems-and-how-to-make-alter-session-work/157301/5 "2009-01-09T10:07:52Z")

</div>

Yeah, the specific post is [Changing default date format in Rails - Rails - Ruby-Forum](http://www.ruby-forum.com/topic/57923#548266)

Unfortunately that’s not working for me. I assume I should just be  
able to do:

Hash::XML\_FORMATTING[‘datetime’] = Proc.new { |datetime|  
datetime.to\_s(:rfc822) }  
errs.to\_xml

I’m still getting the long date in the XML output. I wonder where  
it’s getting that?

I would love to look through the code that’s producing the XML, but  
thus far I cannot figure out where it lives…

---

<div class="post-metadata">

**Author:** ![michael\_teter](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/e47c2d/32.png) [@michael\_teter](https://www.ruby-forum.com/u/michael_teter)\
**Post date:** [January 9, 2009, 11:34am UTC](https://www.ruby-forum.com/t/oracle-date-problems-and-how-to-make-alter-session-work/157301/6 "2009-01-09T11:34:57Z")

</div>

i honestly don’t know. but still i’m eager to learn. here is something  
i found on google:

> [@](#):
>
> If I were facing this dilemma, I would add a method on the  
> model that returns the date in the format that you need it:
> 
> def updated\_at\_js  
> updated\_at.strftime()  
> end
> 
> Then call to\_xml like this:
> 
> p = Person.find\_by\_id(params[:id])  
> p.to\_xml :methods =\> :updated\_at\_js
> 
> so the properly formatted date is included in the xml packet. Then  
> just  
> use that one in your JS.

let me know when you’ve found a solution to that problem.

---

<div class="post-metadata">

**Author:** ![michael\_teter](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/e47c2d/32.png) [@michael\_teter](https://www.ruby-forum.com/u/michael_teter)\
**Post date:** [January 9, 2009, 10:57am UTC](https://www.ruby-forum.com/t/oracle-date-problems-and-how-to-make-alter-session-work/157301/7 "2009-01-09T10:57:47Z")

</div>

I’ve found the place where my date/time/datetime is being formatted (I  
think); however, changing the format isn’t having any effect on  
ActiveRecord::Base.to\_xml()

activesupport-2.0.2/lib/active\_support/core\_ext/hash/conversions.rb  
line 48

I’ve tried changing at runtime via the method listed in previous  
message, and I’ve also tried just modifying conversions.rb as a test.  
Neither change had any effect on to\_xml(). to\_xml() insists on making  
my dates look like Tue Dec 23 00:00:00 -0600 2008

Any clues now?

---

<div class="post-metadata">

**Author:** ![michael\_teter](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/e47c2d/32.png) [@michael\_teter](https://www.ruby-forum.com/u/michael_teter)\
**Post date:** [January 9, 2009, 3:26pm UTC](https://www.ruby-forum.com/t/oracle-date-problems-and-how-to-make-alter-session-work/157301/8 "2009-01-09T15:26:11Z")

</div>

So if I do this:

class Errors \< ActiveRecord::Base  
def crtd\_x  
crtd.strftime("%Y-%m-%d")  
end  
…  
end

errs = Errors.find(:all, :conditions =\> …)

errs.to\_xml(:methods =\> [:crtd\_x, …])

It fails with a NoMethodError: You have a nil object when you didn’t  
expect it!  
The error occurred while evaluating nil.strftime

If I understand correctly, Errors (a subclass of ActiveRecord::Base)  
contains a list of rows, each of which would have a field called  
“crtd”. But at the level I’m calling to\_xml, there is no field  
“crtd”. What I need for my custom function is something like this  
(pseudocode):

def crtd\_x  
current\_error.crtd.strftime("%Y-%m-%d")  
end

I think I’m close. Please advise how I can make crtd\_x() operate on  
the current item in the collection rather than on the whole  
collection.

Thanks much,  
Michael