Monday, December 17, 2007

SOLVED: Rails + MSSQL dates prior to 1970

Intro

MSSQL Microsft SQL Server only stores dates as datetime, As a consequence this is interperted by rails as a Time class, however a Time class cannot have dates prior to 1970, So effectively a date_select etc will raise an exception, for dates prior to 1970. If the column is in reality just a date, why not make it a date.

A have tested hoping rails v2.0 solves this but to no avail, So I needed to go back to my original solution.

My solution relies on manually changing the base type of such fields back to date. Hope this helps others.

What to patch/change

(1) We need to allow a model to change the type of a column. So we patch SQLServerColumn to allow the type to be changed

(2) Edit each model to change, the appropriate columns datatype after_initialize.

(3) Patch "SQLServerColumn" to return a date for a date.

Allow column type to be change

Create lib/sql_server_column_ext.rb

# Patch SqlServerColumn as may need to change column type from datetime to a date, as time cannot be -ve (< 1970) !!!
class ActiveRecord::ConnectionAdapters::SQLServerColumn
  def type=(val)
    @type=val
  end
end 

Edit config/environment.rb

require 'sql_server_column_ext.rb'

Change data type on column, after initalized

Edit model (e.g app/models/patient.rb)

  @@patched_date_of_birth_type=false
  def after_initialize
    if ( !@@patched_date_of_birth_type )
       date_of_birth_column = Patient.columns_hash['date_of_birth']
       date_of_birth_column.type= :date
       @@patched_date_of_birth_type=true
    end
  end

Fix up SQLServerColumn

Edit C:\ruby\lib\ruby\gems\1.8\gems\activerecord-sqlserver-adapter-1.0.0\lib\active_record\connection_adapters\sqlserver_adapter.rb

Implement code to return a date not DateTime when a column has a type of :date

Change "type_cast" method and introduce new method "cast_to_date". The code almost works without this change, but weird things (dates becomming null) because of issues related to converting between Date and Time and vise versa.

def type_cast(value)
   etc...
   when :date      then cast_to_date(value)  # was cast_to_datetime(value)
   etc...
end

def cast_to_date(value)
  return value.to_date if value.is_a?(Date) || value.is_a?(Time) || value.is_a?(DBI::Timestamp) # DateTime is_a? Date
  if value.is_a?(String) 
    date_array = ParseDate.parsedate(value)
    return Date.new(*date_array[0..2]) rescue nil
  end
  return nil
end

Finally

If there is a more elegant solution I would very much like to know

1 comment:

Anonymous said...

A more elegant solution is to include the sqlserver adapter in vendor/plugins/adapters and make the changes to it in there so your app is portable.

Google