Ruby on Rails and SQL Server 2005, or how the database adaptor mishandles DateTime values before 1970 and after 2038

I almost hit a show stopper on a client's project yesterday. We needed a Ruby on Rails application to interface with their data warehouse that runs on SQL Server 2005. We did some initial testing, and we could connect to it fine, so we went ahead and started development... only to discover later down the line that a serious bug prevents us from using the actual data!

We were quite concerned when we found out that it is a known bug, opened over a year ago in the Ruby on Rails tracker, with a high priority and major severity, yet still not fixed. A tentative patch is provided, but reportedly, it does not apply properly. And in our case, even after applying it manually, it still would not work.

Luckily, we managed to get things working with a slight modification, and wrote a monkey patch for it. Here is the code, to include in your environment.rb (You do not need the fix from the bug tracker: it is included in this monkey patch). Insert usual disclaimers here (use at your own risks, yadda yadda...):

# Monkey patch fix to bug #3430
# See http://dev.rubyonrails.org/ticket/3430
# This is adapted from the patch on that page, with
# a slight modification to make it actually work.
class ActiveRecord::ConnectionAdapters::SQLServerColumn

  def type_cast(value)
    return nil if value.nil?
    case type
    when :datetime  then cast_to_time(value.to_s)
    when :timestamp then cast_to_time(value)
    when :time      then cast_to_time(value)
    when :date      then cast_to_date(value)
    when :boolean   then value == true or (value =~ /^t(rue)?$/i) == 0 or value.to_s == '1'
    else super
    end
  end

  def cast_to_date(value)
    Date.new cast_to_time(value)
  end
end