I have observed that DateTime objects when used as arguments to where
conditions or the update_attributes/update_column methods result in SQL
that has the time portion of the DateTime omitted. I can see the root
cause, but it doesn't make sense to me.
In active_record\connection_adapters\sqlserver\quoting.rb the quote_date
method is called:
def quoted_date(value)
if value.acts_like?(:time) && value.respond_to?(:usec)
"#{super}.#{sprintf("%03d",value.usec/1000)}"
elsif value.acts_like?(:date)
value.to_s(:_sqlserver_dateformat)
else
super
end
end
The respond_to?(:usec) call fails for DateTime objects leading to a pure
date formatting of the object.
This led me to try TimeWithZone even though I don't really care about using
a time zone different from the system's local timezone.
Here is an example in the Rails console where the created_at condition in
the generated sql differs when DateTime.now vs DateTime.now.in_time_zone is
used.
Loading development environment (Rails 3.2.8)
irb(main):001:0> Customer.where("created_at < ?", DateTime.now)
Customer Load (4.0ms) EXEC sp_executesql N'SELECT [customers].* FROM
[customers] WHERE (created_at < ''11-09-2012'')'
=> [#<Customer id: 1, name: "Mercy Hospital", crm_account: "12345",
created_at: "2012-11-08 14:31:07", updated_at: "2012-11-08 14:31:07", demo:
true>]
irb(main):002:0> Customer.where("created_at < ?", DateTime.now.in_time_zone)
Customer Load (1.0ms) EXEC sp_executesql N'SELECT [customers].* FROM
[customers] WHERE (created_at < ''2012-11-09 15:39:42.000'')'
=> [#<Customer id: 1, name: "Mercy Hospital", crm_account: "12345",
created_at: "2012-11-08 14:31:07", updated_at: "2012-11-08 14:31:07", demo:
true>]
Is this working as designed? Is there some better way to handle this than
using DateTime.now.in_time_zone?
Thanks.
