Selecting datetime values from SQL Server (year < 1970)

All,

Rails 1.1.6.

Any AR find() call that generates a SELECT * type query against a table
with a DATETIME column in SQL Server whose value occurs before 1970 will
fail, because of the coercion of SQL Server datetime types to Time
values in Ruby.

See sqlserver_adapter.rb line 490
(record[col] = record[col].to_time if record[col].is_a? DBI::Timestamp)

If I remove the coercion (just commenting out that line), I can
successfully execute these find() calls.

I have a couple of questions:

  1. Does anyone know of any adverse effects that I will incur by doing
    this?
  2. Does anyone know when this will be resolved in the sqlserver_adapter?

A related thread that has to do with the input side of this may be found
here: http://www.ruby-forum.com/topic/73435.

Any thoughts?

Still a big bug, in my opinion.

Wes

Further complications forced the following modification to the select()
method in sqlserver_adapter.rb. Basically, it creates a DateTime object
instead of a time object so that older dates will be selected correctly.

private
def select(sql, name = nil)
rows = []
repair_special_columns(sql)
log(sql, name) do
@connection.select_all(sql) do |row|
record = {}
row.column_names.each do |col|
record[col] = row[col]
#Coerce to DateTime in order to handle old datetime values. -
10/19/06 WG
#record[col] = record[col].to_time if record[col].is_a?
DBI::Timestamp
record[col] = DateTime.new(record[col].year, record[col].month,
record[col].day, record[col].hour, record[col].minute,
record[col].second, record[col].fraction) if
record[col].is_a?(DBI::Timestamp)
end
rows << record
end
end
rows
end

On the input side of things, the following change is required to the
quote() method in sqlserver_adapter.rb - the change is to put the “when
Time, DateTime” before “when Time”. Since DateTime is_a Time, the
input formatting was not correct for true DateTime values.

def quote(value, column = nil)
case value
when String
if column && column.type == :binary &&
column.class.respond_to?(:string_to_binary)
“’#{quote_string(column.class.string_to_binary(value))}’”
else
“’#{quote_string(value)}’”
end
when NilClass then “NULL”
when TrueClass then ‘1’
when FalseClass then ‘0’
when Float, Fixnum, Bignum then value.to_s
when Time, DateTime then “’#{value.strftime(”%Y-%m-%d
%H:%M:%S")}’"
when Date then “’#{value.to_s}’”
else “’#{quote_string(value.to_yaml)}’”
end
end

Wes

On the input side of things, the following change is required to the
quote() method in sqlserver_adapter.rb - the change is to put the “when
Time, DateTime” before “when Time”. Since DateTime is_a Time, the
input formatting was not correct for true DateTime values.

def quote(value, column = nil)

when Time, DateTime then “’#{value.strftime(”%Y-%m-%d
%H:%M:%S")}’"
when Date then “’#{value.to_s}’”
else “’#{quote_string(value.to_yaml)}’”
end
end

Wes

To clarify: This change should successfully allow datetime values to be
updated in the database.

This was just what I was looking for, thanks!

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs