The sqlserver datetime issue... Anyone with a solution?


#1

Hey :slight_smile:

I’m getting annoyed with this datetime issue in sqlserver. That is,
not being able to use datetimes from before 1970 or after 2038. It’s
pretty difficult to not just do that, as we have more than two and a
half million books in a database, many of them written before 1970.

I’ve Googled around, but I really can’t seem to find that anyone has
taken this issue seriously and made some kind of fix.

I saw this one,

http://snippets.dzone.com/posts/show/2140

…but I don’t know what “Stick this in a plugin to use with Rails”
really means. I tried putting it in a file in lib and requiring it
on the model, then in environment.rb, but the code was never executed
(I put in a raise statement to let me know if it was).

Also, I saw this one,

http://dev.rubyonrails.org/ticket/4831

…I applied the patch, but it made no difference.

Have you guys struggled with this, and have a fix for it? It bugs
me :’(

Cheers,
Daniel


#2

On Feb 24, 2007, at 11:27 AM, Daniel Smedegaard B. wrote:

I saw this one,
http://dev.rubyonrails.org/ticket/4831

…I applied the patch, but it made no difference.

Have you guys struggled with this, and have a fix for it? It bugs
me :’(

Cheers,
Daniel

It sounds like you don’t need a :datetime column (which converts to a
Ruby Time), but only a :date column (which converts to a Ruby Date)
or just a year as an :integer column.

I’m using MySql, and the rdoc for the SQLServer adapter has this code
fragment: (reformatted for inclusion in email)

  # File vendor/rails/activerecord/lib/active_record/

connection_adapters/sqlserver_adapter.rb, line 448
448: def add_column(table_name, column_name, type, options = {})
449: add_column_sql = “ALTER TABLE #{table_name} ADD
#{quote_column_name(column_name)}
#{type_to_sql(type, options[:limit],
options[:precision],
options[:scale])}”
450: add_column_options!(add_column_sql, options)
451: # TODO: Add support to mimic date columns, using
constraints to mark them as such in the database
452: # add_column_sql << " CONSTRAINT
ck__#{table_name}__#{column_name}__date_only
CHECK ( CONVERT(CHAR(12),
#{quote_column_name(column_name)},
14)=‘00:00:00:000’ )" if type == :date
453: execute(add_column_sql)
454: end

Perhaps you could experiment with the code aroung line 452 and submit
a working patch.

-Rob

Rob B. http://agileconsultingllc.com
removed_email_address@domain.invalid


#3

Is this a solution?

http://www.ruby-forum.com/topic/144319