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

rubyonrails-talk
1

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?

2

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

ActiveSupport::CoreExtensions::date::Conversions::DATE_FORMATS.update (:default => '%Y-%m-%d')

3

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<errors>\n <error> \n <crtd>Wed Jan 07 00:00:00 -0600 2009</crtd> ...]

So my problem is in to_xml...

4

a guy in this thread had the same problem and obviously fixed it (scroll down to the end).

5

Yeah, the specific post is Changing default date format in Rails - Rails - Ruby-Forum

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...

6

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?

7

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

[quote] 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(<some format>) 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. [/quote]

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

8

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