Why do DateTime objects get their time truncated when used to build sql?

[Ben Grommes]

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.

[Ken Collins]

When in Ruby try to use the preferred Time object, I think DateTime in general is history and in some platforms does not respond to #usec or even have the capacity to hold the range Time can.

- Ken