I am writing a web service using Sinatra that will be consumed by an Android application. The database being used in Microsoft SQL Server.
The stored procedure call is being made in Ruby:
sql = "exec GetTraces '#{id}', '#{start_date}', '#{end_date}'"
result = ConnectionManager.connection.select_all(sql)
The GetTraces stored procedure returns a column containing a datetime object. When the server and client exist in the same time zone the correct results are returned. Incorrect results are returned when the server and client are in different time zones.
For example if I use 2013-10-16T06:1500Z for the start date and 2013-10-16T09:1500Z as the end date then I should get a date time within that range. When the client and server are in different time zones I get the following:
2013-04-09T00:03:11+00:00
I manually tested the stored procedure and it returns the correct results. The results displayed in Ruby are not what are returned from the stored procedure. Activerecord and/or Tiny tds appear to be parsing the date times incorrectly.
Here is an example of a datetime that is returned when the stored procedure is manually executed:
2013-10-16 09:10:51.553
I have overridden ActiveRecord with the following to fix previous issues with parsing datetimes from SQL Server. I found this solution online.
module ActiveRecord
module ConnectionAdapters
class ColumnWithIdentity
def cast_to_time(value)
return value if value.is_a?(Time) or value.is_a?(DateTime)
time_array = ParseDate.parsedate(value)
time_array[0] ||= 2000
time_array[1] ||= 1
time_array[2] ||= 1
Time.send(Base.default_timezone, *time_array) rescue DateTime.new(*time_array[0..5]) rescue nil
end
def cast_to_datetime(value)
if value.is_a?(Time) or value.is_a?(DateTime)
if value.year != 0 and value.month != 0 and value.day != 0
return value
else
return Time.mktime(2000, 1, 1, value.hour, value.min, value.sec) rescue nil
end
end
return cast_to_time(value) if value.is_a?(Date) or value.is_a?(String) rescue nil
value
end
end
end
end
Any insight into this matter is appreciated. Thank you!
I was able to work around the issue by running the script on Windows instead of Linux. The returned date and times are now correct.