Patch for AR SQLServer adapter to allow for < 1970 datetimes

Rails
1

All,

= Rails 1.2.6

I’ve been down this road of not being able to get the SQLServer AR
driver to support pre-1970 SQLServer datetime fields before back in
Rails 1.1.6 (Selecting datetime values from SQL Server (year < 1970) - Rails - Ruby-Forum), but now I’m finally
updating this app. to Rails 1.2 and beyond.

So…below is a patch to the current 1.0.0 version of
sqlserver_adapter.rb (from the new separate AR adapter Rails 2.0-style
gem) that appears (in my app. so far) to successfully allow for the
successful manipulation of < 1970 datetime values in Rails. It requires
mods. to both the “cast_to_datetime” and “string_to_time” methods, and
as you can see, simply returns DateTime objects instead of Time objects
(which are limited to 1970 and later).

I’m not sure why the casts to time are there in the first place - my
guess is that the way that MySQL handles dates influences how this
SQLServer driver is handling dates.

I will try and make time to set up a proper patch submission in Trac at
some point, but really pressed for time these days.

Thanks,
Wes

---- BEGIN PATCH ----

Index: sqlserver_adapter.rb

— sqlserver_adapter.rb (revision 8940)
+++ sqlserver_adapter.rb (working copy)
@@ -104,8 +104,7 @@
end

     if value.is_a?(DateTime)

  •      return Time.mktime(value.year, value.mon, value.day,

value.hour, valu
e.min, value.sec)

  •      #return DateTime.new(value.year, value.mon, value.day,

value.hour, va
lue.min, value.sec)

  •      return DateTime.new(value.year, value.mon, value.day,

value.hour, val
ue.min, value.sec)
end

     return cast_to_time(value) if value.is_a?(Date) or

value.is_a?(String)
rescue nil
@@ -116,7 +115,7 @@

   def self.string_to_time(value)
     if value.is_a?(DateTime)

  •      return Time.mktime(value.year, value.mon, value.day,

value.hour, valu
e.min, value.sec)

  •      return DateTime.new(value.year, value.mon, value.day,

value.hour, val
ue.min, value.sec)
else
super
end

---- END PATCH ----

2

Wes,

Obie F. addressed this specifically in “The Rails Way.” You
can override the mapping without having to dig into the adapter itself
if you like. Drop the following into a *.rb file and require it in
your environment.rb:

require ‘date’
class ActiveRecord::ConnectionAdapters::Column
def self.string_to_time(string)
return string unless string.is_a?(String)
time_array = ParseDate.parsedate(string)[0…5]
begin
Time.send(Base.default_timezone, *time_array)
rescue
DateTime.new(*time_array) rescue nil
end
end
end

Obviously having this self contained and out of a gem (that you might
update in the future!) has its advantages.

As for why Time instead of DateTime… performance. Time is running
on the bare steel (in C) whereas DateTime is comparatively slower as a
pure Ruby implementation. That’s why the workaround above defers to
Time if possible.

On Feb 28, 1:27 pm, Wes G. [email protected]

3

Thanks - this is good info.

I don’t agree with favoring Time since it clashes with the expectation
of what SQL Server datetimes can hold. Isn’t the rule “make it right,
then make it fast?” As it stands, we’re crippling the datetime range of
SQL Server because of Ruby performance issues.

Perhaps the adapter should support both type mappings, and default to
the DateTime mapping, and provide a configurable switch that can be set
to favor the Time mapping. That way, the functionality of the datetime
column is intact by default and people who only deal with dates > 1970
can still get the performance boost if they want it.

Wes

4

Trust me, I feel the pain. I’m… well, let’s just say my b’day lies
outside the range that Date supports and I constantly run into issues
trying to display how old I am.
:slight_smile:

Hopefully the implementation of DateTime will improve soon so that it
can become the default.

On Mar 4, 11:51 am, Wes G. [email protected]