DZone Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world

Snippets has posted 5883 posts at DZone. View Full User Profile

Fix For ActiveRecord SQL Server Adapter Dates

06.05.2006
| 13349 views |
  • submit to reddit
        The SQL Server adapter for ActiveRecord uses Time objects to cast dates from the db. This fails for dates before 1970, thus some birthdates come back as nil. This is some kludge to use a DateTime in that case so we still get the value.

A better approach may be to convert this code to use DateTime objects exclusively, but I'm not sure of the speed implications of doing so. The code below first tries to cast the value to a Time object; if that fails, it tries a DateTime object; if that fails, it returns nil.

Stick this in a plugin to use with Rails.

module ActiveRecord
  module ConnectionAdapters
    class ColumnWithIdentity
      def cast_to_time(value)
        return value if value.is_a?(Time) or value.is_a?(DateTime)
        time_array = ParseDate.parsedate(value)
        time_array[0] ||= 2000
        time_array[1] ||= 1
        time_array[2] ||= 1
        Time.send(Base.default_timezone, *time_array) rescue DateTime.new(*time_array[0..5]) rescue nil
      end
      def cast_to_datetime(value)
        if value.is_a?(Time) or value.is_a?(DateTime)
          if value.year != 0 and value.month != 0 and value.day != 0
            return value
          else
            return Time.mktime(2000, 1, 1, value.hour, value.min, value.sec) rescue nil
          end
        end
        return cast_to_time(value) if value.is_a?(Date) or value.is_a?(String) rescue nil
        value
      end
    end
  end
end
    

Comments

Snippets Manager replied on Thu, 2007/03/01 - 11:38pm

Cool! Only problem was when dates are non-parseable, then the date_array is [nil, nil, ...], which DateTime.new accepts and creates a date of -4712. Here's a fix, that checks for that: module ActiveRecord module ConnectionAdapters #:nodoc: # An abstract definition of a column in a table. class Column def self.string_to_time(string) return string unless string.is_a?(String) date_array = ParseDate.parsedate(string) return nil if (date_array.compact.empty?) # treat 0000-00-00 as nil DateTime.new(*date_array.compact) rescue nil end end end end

Snippets Manager replied on Mon, 2012/05/07 - 2:24pm

Absolutely wonderful! I just used your code for a MySQL-based app and it worked beautifully. Thanks.

Snippets Manager replied on Thu, 2006/09/28 - 6:34am

my stab at this problem was this. module ActiveRecord module ConnectionAdapters #:nodoc: # An abstract definition of a column in a table. class Column def self.string_to_time(string) return string unless string.is_a?(String) date_array = ParseDate.parsedate(string) # treat 0000-00-00 as nil DateTime.new(*date_array.compact) rescue nil end end end end