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

**URL:** <https://www.ruby-forum.com/t/the-sqlserver-datetime-issue-anyone-with-a-solution/86690>\
**Category:** Rails\
**Created:** [February 24, 2007, 5:28pm UTC](https://www.ruby-forum.com/t/the-sqlserver-datetime-issue-anyone-with-a-solution/86690 "2007-02-24T17:28:50Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![danielb](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/c57346/32.png) [@danielb](https://www.ruby-forum.com/u/danielb)\
**Post date:** [February 24, 2007, 5:28pm UTC](https://www.ruby-forum.com/t/the-sqlserver-datetime-issue-anyone-with-a-solution/86690/1 "2007-02-24T17:28:50Z")

</div>

Hey 🙂

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](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](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

---

<div class="post-metadata">

**Author:** ![danielb](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/c57346/32.png) [@danielb](https://www.ruby-forum.com/u/danielb)\
**Post date:** [February 24, 2007, 7:28pm UTC](https://www.ruby-forum.com/t/the-sqlserver-datetime-issue-anyone-with-a-solution/86690/2 "2007-02-24T19:28:04Z")

</div>

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

> I saw this one,  
> [http://dev.rubyonrails.org/ticket/4831](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](http://agileconsultingllc.com)  
removed\_email\_address@domain.invalid

---

<div class="post-metadata">

**Author:** ![danielb](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/c57346/32.png) [@danielb](https://www.ruby-forum.com/u/danielb)\
**Post date:** [February 28, 2008, 7:28pm UTC](https://www.ruby-forum.com/t/the-sqlserver-datetime-issue-anyone-with-a-solution/86690/3 "2008-02-28T19:28:27Z")

</div>

Is this a solution?

[http://www.ruby-forum.com/topic/144319](http://www.ruby-forum.com/topic/144319)