[PATCH] SqlServer DB adaptor mishandles DateTime values before 1970 and after 2038

We want RoR to handle the birthdate of our bosses ;)

Whoa, no progress on this?

It looks like Time us used for the timezone, DateTime doesn't replication that functionality. The fact that nil is used to rescue means validations fail as being empty.

Geh, mucho debugging needed.

Ok, I think this patch addresses things well enough. It avoids using DateTime at all and keeps datetime columns as Time, and Date columns as Date.

There appears to be a related issue on line 113 or so of the abstract\schema_definitions.rb file:

Time.send(Base.default_timezone, *time_array) rescue nil

I fixed this for my site (SQL server only) by replacing that line with:

if time_array[3] == 0 && time_array[4] == 0 && time_array[5] == 0
  Date.new(*time_array[0..2]) rescue nil
else
  Time.send(Base.default_timezone, *time_array) 
  rescue Datetime.new(*time_array)
  rescue nil
end

As I don't have any other databases handy to test the behavior, I don't know what sort of obnoxious things this change might cause in other circumstances. I am aware that I am a stoneheaded coder, so if someone can offer a more elegant and/or correct way of handling it, I would be very appreciative (especially if a fix made its way into the distribution).

The attached patch doesn't apply cleanly on revision #6639, and I wonder if it actually fixes anything: Pure date columns doesn't exist in SQL Server 2000, the patch seems to assume they do. I imagine they exist in SQL Server 2005?

Also, I have a few questions about this issue and proposed fixes, probably mainly due to my lack of understanding of ruby and SQL Server types:

The SQL Server adapter at the moment attempts to typecast datetime columns using the cast_to_datetime method, which sounds like a good approach. However, the cast_to_datetime returns Time objects (and not datetime ones as the method name suggests).

Would it be a sensible fallback to typecast to DateTime instead of Time for those cases where casting to Time returns the ArgumentError: time out of range ? I mean, who really needs timezone support for times that are more than 30 years old or 30 years into the future?

If we do need timezone support for those times, aren't we pretty much f'd? The only date/time object in Ruby that does support timezones is Time, and that obviously doesn't work outside the 1970-2038 range. Date doesn't support timezones, neither does DateTime.

From where I am sitting, it makes sense to return DateTime objects when casting to Time doesn't work, but I wonder if I am missing something obvious?

I monkey-patched my existing Rails 1.2.3 based on the patch submitted by mixonic, but it did not work: I was getting the following errors

>> member.Birthday
NoMethodError: private method `gsub!' called for #<DateTime: 4875331/2,0,2299161>
        from /usr/lib/ruby/1.8/date/format.rb:255:in `_parse'
        from /usr/lib/ruby/1.8/parsedate.rb:47:in `parsedate'
        from /var/lib/gems/1.8/gems/activerecord-1.15.3/lib/active_record/connection_adapters/sqlserver_adapter.rb:89:in `cast_to_time'
        from ./script/../config/../config/../lib/overrides.rb:21:in `type_cast'
        from /var/lib/gems/1.8/gems/activerecord-1.15.3/lib/active_record/base.rb:1873:in `read_attribute'
        from /var/lib/gems/1.8/gems/activerecord-1.15.3/lib/active_record/base.rb:1849:in `method_missing'
        from (irb):2

I got it to work by replacing line 81 in the patch by:

    when :datetime  then cast_to_time(value.to_s)

I am using date_select for a date_of_birth field on one of my forms. If I enter some birth dates into the DB manually and then edit the record in my application, the dates are loaded and displayed correctly. However I get this error when I try to save a record with any dates <1970:

1 error(s) on assignment of multiparameter attributes

f:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.3/lib/active_record/base.rb:2094:in `execute_callstack_for_multiparameter_attributes'
f:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.3/lib/active_record/base.rb:2074:in `assign_multiparameter_attributes'
f:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.3/lib/active_record/base.rb:1675:in `attributes='
f:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.3/lib/active_record/base.rb:1505:in `initialize_without_callbacks'
f:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.3/lib/active_record/callbacks.rb:225:in `initialize'
f:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.3/lib/active_record/associations/has_many_association.rb:13:in `new'
f:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.3/lib/active_record/associations/has_many_association.rb:13:in `build'
#{RAILS_ROOT}/app/controllers/service_request_controller.rb:255:in `build_services'
#{RAILS_ROOT}/app/controllers/service_request_controller.rb:255:in `each_value'
#{RAILS_ROOT}/app/controllers/service_request_controller.rb:255:in `build_services'
#{RAILS_ROOT}/app/controllers/service_request_controller.rb:96:in `create'

So in execute_callstack_for_multiparameter_attributes I changed line 2087 to this:

send(name + "=", Time == klass ? (@@default_timezone == :utc ? klass.utc(*values) : klass.local(*values)) : klass.new(*values)) rescue send(name + "=", DateTime.new(*values))

After that change, I get this error:

argument out of range

f:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.3/lib/active_record/connection_adapters/sqlserver_adapter.rb:105:in `mktime'
f:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.3/lib/active_record/connection_adapters/sqlserver_adapter.rb:105:in `cast_to_datetime'
f:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.3/lib/active_record/connection_adapters/sqlserver_adapter.rb:78:in `type_cast'

On line 105 of sqlserver_adapter.rb I added "return value" after the call to mktime

I'm not sure if this will break anything, but my tests seem to be working so far.

Related Infos: ruby 1.8.5, rails 1.2.3, SQL Server 2000 on Windows XP; activerecord gem is at 1.15.3.

Hokay so, right after my previous post, I noticed that there is a problem.

>> test1 = Participant.find(147)
=> #<Participant:0x47100e4 @attributes={"dob"=>"1910/11/24 00:00:00", "title"=>"", "id"=>147, first_name"=>"", "last_name"=>"", "affiliation"=>"", "service_request_id"=>"51"}>
>> test1.dob
=> nil
>> test1.dob
=> #<DateTime: 4837999/2,0,2299161>
>>

The dob method always returns nil the first time it is called. If I were to load a second Participant record, its dob method would return a DateTime object the first time called. So right now, I am calling the dob method once from within the controller, right before rendering. Yuck, but I need it to work right now. =/

Hi, I thing the following plugin will solve the problems. The code was originally written by Tim Morgan and I made a plugin available. I tested it sucessfully against SQLServer 2000 with DBI and ODBC mode.

Additionally I incoporated a small issue (scopy_identity() instead of @@identity) which could lead into getting the wrong primary key when inserting a new record.

It would be great ich the solution can be incoporated in the next version od ActiveRecord. Please see:

http://usefulrailsplugins.googlecode.com/svn/trunk/

Best regards Chris

I can confirm that this is still an issue with rails 2.0.1 and the latest version of sqlserver adapter (both installed as a gem or in plugins/adapters) with SQL Server 2000 (which only has a datetime column type, no plain date). The above plugin doesn't seem to be working for me as I still get '1 error(s) on assignment of multiparameter attributes' when using date_select and selecting a date before 1970.

I've been working on this error for most of the day with increasing frustration. I am developing an application for use in nursing homes so obviously I need date of birth's before 1970. :-) The fixes offered above aren't working because, actually, convert_to_time() and convert_to_datetime() are not being called during validation. I checked this by placing invalid statements inside both methods and ruby does not throw any exceptions so it is not even making it to the fixed code. Debugging from script/console, here is what I've gotten to:

>> @patient
=> #<Patient id: 8, first: "Nathan", middle: "", last: "Smithington", date_of_birth: "1977-01-23 00:00:00", date_of_admission: "2007-10-23 00:00:00", social_security: 111111122, medicare_id: nil, medicaid_id: nil, user_group_id: 1, facility_id: 1, legal_guardian: "", updated_at: "2008-01-15 18:02:33">
>> params["patient"]
=> {"date_of_birth(2i)"=>"1", "date_of_birth(3i)"=>"23", "date_of_birth(1i)"=>"1927"}
>> @patient.update_attributes!(params["patient"])
ActiveRecord::RecordInvalid: Validation failed: Date of birth is invalid
        from c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/validations.rb:946:in `save_without_transactions!'
        from c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/transactions.rb:112:in `save!'
        from c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/connection_adapters/abstract/database_statements.rb:66:in `transaction'
        from c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/transactions.rb:80:in `transaction'
        from c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/transactions.rb:100:in `transaction'
        from c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/transactions.rb:112:in `save!'
        from c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/transactions.rb:120:in `rollback_active_record_state!'
        from c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/transactions.rb:112:in `save!'
        from c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/base.rb:2040:in `update_attributes!'
        from (irb):69
        from :0

The rails update calls update_attributes but I used update_attributes! in order to see the stack trace from the exception. I'm kind of at a loss for what to do next. I've tried trolling through the source code for activerecord, but I am not sure what statement is creating the exception at the lowest level. It looks to be hidden in a private method somewhere, which I can't test from the console for obvious reasons. What part of the code runs validation for a date/time/datetime object? Can someone help?

EDIT: I meant of course cast_to_time() and cast_to_datetime(), not convert_to_*.

I fixed this problem by overriding klass method in the ActiveRecord::ConnectionAdapters::SQLServerColumn class to return DateTime. I understand from some reading that DateTime is very slow to work with, but I don't see any other options. I don't know how to submit my patch 'officially' (this is my first one), so here is the code if anyone is interested:

module ActiveRecord
  module ConnectionAdapters
    class SQLServerColumn < Column
      
      # Returns the Ruby class that corresponds to the abstract data type
      # changed to use DateTime for :datetime type
      def klass
        case type
          when :integer       then Fixnum
          when :float         then Float
          when :decimal       then BigDecimal
          when :datetime      then DateTime # was Time
          when :date          then Date
          when :timestamp     then Time
          when :time          then Time
          when :text, :string then String
          when :binary        then String
          when :boolean       then Object
        end
      end
    end
  end
end

I also made a few other changes to the plugin posted on 09/17/07, but I don't have permission to that subversion repository to commit my changes. I could post the patch as an attachment here but that doesn't seem to make much sense either. If you're curious, the original definition of klass is here: gems\activerecord-2.0.2\lib\active_record\connection_adapters\abstract\schema_definitions.rb:41