# Selecting datetime values from SQL Server (year \< 1970)

**URL:** <https://www.ruby-forum.com/t/selecting-datetime-values-from-sql-server-year-1970/72830>\
**Category:** Rails\
**Created:** [October 19, 2006, 8:01am UTC](https://www.ruby-forum.com/t/selecting-datetime-values-from-sql-server-year-1970/72830 "2006-10-19T08:01:17Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![weyus](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/w/7ab992/32.png) [@weyus](https://www.ruby-forum.com/u/weyus)\
**Post date:** [October 19, 2006, 8:01am UTC](https://www.ruby-forum.com/t/selecting-datetime-values-from-sql-server-year-1970/72830/1 "2006-10-19T08:01:17Z")

</div>

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: [Inserting datetime value into SQL Server - Rails - Ruby-Forum](http://www.ruby-forum.com/topic/73435).

Any thoughts?

Still a big bug, in my opinion.

Wes

---

<div class="post-metadata">

**Author:** ![weyus](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/w/7ab992/32.png) [@weyus](https://www.ruby-forum.com/u/weyus)\
**Post date:** [October 24, 2006, 12:15am UTC](https://www.ruby-forum.com/t/selecting-datetime-values-from-sql-server-year-1970/72830/2 "2006-10-24T00:15:04Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![weyus](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/w/7ab992/32.png) [@weyus](https://www.ruby-forum.com/u/weyus)\
**Post date:** [October 24, 2006, 12:24am UTC](https://www.ruby-forum.com/t/selecting-datetime-values-from-sql-server-year-1970/72830/3 "2006-10-24T00:24:06Z")

</div>

> 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.

---

<div class="post-metadata">

**Author:** ![weyus](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/w/7ab992/32.png) [@weyus](https://www.ruby-forum.com/u/weyus)\
**Post date:** [March 7, 2007, 11:12pm UTC](https://www.ruby-forum.com/t/selecting-datetime-values-from-sql-server-year-1970/72830/4 "2007-03-07T23:12:58Z")

</div>

This was just what I was looking for, thanks!