Rails Model.save get datetime convertion error: “A

问题:

I've a problem with SQL Server and Rails.

Rails and SQL Server seems to save date format in different ways:

Rails way:  2011-12-15
SQL Server: 15-12-2011

As I'm running SQL Server in Brazilian Portuguese so my problem might exist only for brazilian people.

Whenever I do any Model.save on Rails, I'm getting this error:

A conversão de um tipo de dados varchar em um tipo de dados 
datetime resultou em um valor datetime fora do intervalo.:  <my query here>

As Rails insert/update the created_at and updated_at columns, I always get this error.

Does anyone knows how to solve this?

I'm running jRuby on activerecord-jdbcmssql-adapter.

回答1:

it's dead simple...just create a file under config/initializer with the name you want, I use datetime_format.rb. With this line:

Time::DATE_FORMATS[:db]= '%d-%m-%Y %H:%M:%S'

This will overwrite the default datetime format for the DB.

回答2:

Just found a solution:

I've created a file named "sqlserver_dateformat.rb" in Rails' lib/ folder and added this:

class ActiveRecord::Base
  before_save :set_sqlserver_dateformat

  def set_sqlserver_dateformat
    ActiveRecord::Base.connection.exec_query("set DATEFORMAT ymd")
  end
end

Now on everymodel I've required it:

require 'sqlserver_dateformat'
class User < ActiveRecord::Base
    # ...
end

I known, it's a monkey patch, but hey, it works! =]