Datetime in odbc format

[marcomd]

I have weird issues with regional settings using this format:
YYYY-MM-DD HH:MM:SS.mmm

if i use italian settings, insert or update query give back this
message:
ODBC::Error: 22008 (242) [Microsoft][SQL Server Native Client 10.0]
[SQL Server]The conversion of a char data type to a datetime data type
resulted in an out-of-range datetime value.

I don't know why but when i used ADO i didn't have this issue,
datetime had this format:
YYYYMMDD HH:MM:SS.mmm

Could anyone help me to find where datetime string is built? I could
try to remove that '-' or if anyone could tell me another more elegant
way to solve

--
You received this message because you are subscribed to the Google Groups "Rails SQLServer Adapter" group.
To post to this group, send email to rails-sqlse...@googlegroups.com.
To unsubscribe from this group, send email to rails-sqlserver-a...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/rails-sqlserver-adapter?hl=en.

[Ken Collins]

Well typically in a rails app or just raw from ActiveRecord you set the date/time attribute of an object using either a Date/Time object. Perhaps I would need to see your raw parameters and full stack trace to understand the details and help more. A few things come to mind

1) The adapter is well tested and passes all the ActiveRecord tests as well as it's own. This would mean it passes many tests that cover assignment of attributes to/from date/time objects in many ways including string. So I would feel good that you are just hitting an edge case that needs working around or knowing a more correct way.

2) The error looks like something I've seen smalldatetime data types throw. What is your underlying schema for that attribute?


- Ken

[marcomd]

Hi Ken, i think this is a oddity of my sqlserver 2000 because i have
same issues even trying activerecord queries with the ms tool: sql
queries analyzers.

I didn't understand why odbc mode produces a different date format
from ado. I have to say i always used version 1.0.0.9250 of the
adapter. It was very old but it have always worked fine for my
purpose. I decided to update to new version since it have 2008 support
and it seems was faster.

Btw, I solved in a ugly way, looking for dates into sql and removing
'-' separators. I put these lines at the beginning of execute and
select def, into sqlserver_adapter:

regexp = '(19|20)\d\d[- \.](0[1-9]|1[012])[- \.](0[1-9]|[12]
[0-9]|3[01])'
while sql =~ /#{regexp}/
str = sql.match(/#{regexp}/).to_s
sql.sub! str, str.gsub('-','')
end

[Ken Collins]

Great, I'm glad it's working for you. Had no idea you were not even using something anywhere near close to the current adapter too. BTW, I think you fix should be further up the chain somewhere vs in the adapter. My advice would be to spend some time and find out where, possibly view/controller code. Either way, glad your on the current band wagon :)

- Ken

[KD.Gun...@zwick-edelstahl.de]

Hi Marc,

> YYYY-MM-DD HH:MM:SS.mmm

this format is known as ANSI SQL Standard but sadly in MS SQL Server
it will be interpreted depending on the current LANGUAGE for datetime
fields ( see http://msdn.microsoft.com/en-us/library/ms180878.aspx )

It would be better to use the ISO 8601 format, which is an
international standard.

I am currently looking at the source code of the sqlserver_adapter and
would suggest that the change goes into the quoted_date function, but
as I am Ruby beginner I am not quit sure about it ...

[Ken Collins]

Nice find. I added comments to this ticket you started here:
http://github.com/rails-sqlserver/2000-2005-adapter/issues#issue/37

- Ken

[KD.Gun...@zwick-edelstahl.de]

Hi Ken,

I have some good new and some bad news.

The good one first:
I am in contact with Christian Werner and I modified ruby-odbc so that
it no longer throws an error on VARCHAR(MAX) fields

The bad one is:
I am still struggling with the datetime problem, specially with the
fraction of a second part.

> As far as I as I understand Ruby has a class Date and a class Time but NO Timestamp class
The class Time has a property usec which returns the one millionth
part of a second

> Rails adds a class ActiveSupport::TimeWithZone which also understands usec

> ruby-odbc has a Class ODBC::Date a class ODBC::Time and ODBC::TimeStamp but without time zone offsets
ODBC::Time neither understands usec nor fraction
ODBC::TimeStamp understands fraction

> and the SQLServer itself understands several different Date and Time formats,
and the most portable of them is the ISO8601 format

and so there is the need for some conversions between these formats...

But how will handle ActiveRecord datetime values internally ??
Looking through the tests I find:
In adapter_test_sqlserver.rb
@db_datetime_003 = '2012-11-08 10:24:36.003'
assert_equal @db_datetime_003,
existing_003.datetime_before_type_cast
In base_test.rb
assert_equal developer.created_at.to_s(:db) ,
developer.attributes_before_type_cast["created_at"]
So ActiveRecord will store them internally as Strings ???!?!?

Puzzled by the magic RoR does .. Klaus

[Ken Collins]

Not ignoring you here, still working on the Rails3 work and will have time to check in on this later. Just FYI.

- Ken

[KD.Gun...@zwick-edelstahl.de]

If I may help you working on the Rails3 adapter, please tell me so.
As you may have seen, I have set up a test environment for Rails3 on
my windows system.
And there I am still struggling with the datetime problem.

Found it on MS MSDN:
"The setting for SET DATEFORMAT determines how date values are
interpreted. If the order does not match the setting, the values are
not interpreted as dates because they are out of range or the values
are misinterpreted. For example, 12/10/08 can be interpreted as !!!
one of six dates !!!, depending on the DATEFORMAT setting."

-------------------
PS:
I have found in database_statements :
def handle_to_fields_and_rows_odbc(handle)
...
if value.is_a? ODBC::TimeStamp
row[i] = value.to_sqlserver_string
end

and in odbc.rb you are defining:

module ActiveRecord
module ConnectionAdapters
module SQLServerCoreExtensions
module ODBC

module TimeStamp
def to_sqlserver_string
date, time, nanoseconds = to_s.split(' ')
"#{date} #{time}.#{sprintf("%03d",nanoseconds.to_i/
1000000)}"
end
end

So here you are converting the ODBC::TimeStamp in a string, splitting
up the string, converting to integer, building a string again ...
Wouldn't it be nicer if we implement the function to_sqlserver_string
( and may to_iso8601 ) directly in the ruby-odbc driver ?

Greetings

Klaus

[Ken Collins]

Hey Klaus,

If I may help you working on the Rails3 adapter, please tell me so.
As you may have seen, I have set up a test environment for Rails3 on
my windows system. 

I just sent an email about that out.

And there I am still struggling with the datetime problem. Found it on MS MSDN:

"The setting for SET DATEFORMAT determines how date values are
interpreted. If the order does not match the setting, the values are
not interpreted as dates because they are out of range or the values
are misinterpreted. For example, 12/10/08 can be interpreted as !!!
one of six dates !!!, depending on the DATEFORMAT setting."

Sorry to hear that your still having issues :( I am sure I can be more help once I get the rails3 work behind us. I I use the #user_options method of the connection I can see this in my local DB 

{"isolation_level"=>"read committed", "concat_null_yields_null"=>"SET", "quoted_identifier"=>"SET", "ansi_padding"=>"SET", "ansi_null_dflt_on"=>"SET", "datefirst"=>"7", "language"=>"us_english", "textsize"=>"64512", "lock_timeout"=>"-1", "ansi_nulls"=>"SET", "dateformat"=>"mdy", "ansi_warnings"=>"SET"}

Perhaps we can easily case off that "dateformat" to help the adapter cast correctly? Seems to me we could easily do that in the #quoted_date for the inbound data and then the coercion of the ODBC::TimeStamp on the way out.

-------------------
PS:
I have found in database_statements :
       def handle_to_fields_and_rows_odbc(handle)
          ...
             if value.is_a? ODBC::TimeStamp
               row[i] = value.to_sqlserver_string
             end

and in odbc.rb you are defining:

module ActiveRecord
 module ConnectionAdapters
   module SQLServerCoreExtensions
     module ODBC

       module TimeStamp
         def to_sqlserver_string
           date, time, nanoseconds = to_s.split(' ')
           "#{date} #{time}.#{sprintf("%03d",nanoseconds.to_i/
1000000)}"
         end
       end

So here you are converting the ODBC::TimeStamp in a string, splitting
up the string, converting to integer, building a string again ...
Wouldn't it be nicer if we implement the function to_sqlserver_string
( and may to_iso8601 ) directly in the ruby-odbc driver ?

Perhaps, but this was an acceptable monkey patch for me and felt it gives us enough flexibility moving forward to adapt and change. This final string coercion is ultimately handed back up to ActiveRecord's fast string to date time helpers too. Any way that works if fine by me, but so far I do not see a big issue with this yet.

 - Ken

[KD.Gun...@zwick-edelstahl.de]

Hi Ken,

>
> {"isolation_level"=>"read committed", "concat_null_yields_null"=>"SET", "quoted_identifier"=>"SET", "ansi_padding"=>"SET", "ansi_null_dflt_on"=>"SET", "datefirst"=>"7", "language"=>"us_english", "textsize"=>"64512", "lock_timeout"=>"-1", "ansi_nulls"=>"SET", "dateformat"=>"mdy", "ansi_warnings"=>"SET"}
>
> Perhaps we can easily case off that "dateformat" to help the adapter cast correctly? Seems to me we could easily do that in the #quoted_date for the inbound data and then the coercion of the ODBC::TimeStamp on the way out.
>

In my test system I have changed #quoted_date , so that it will
convert the data to ISO8601 format which is independent of the
language settings. BUT

Problem 1:
- it won't catch situations where someone is using connection.execute
See your test 'For chronic data types' :
@connection.execute("INSERT INTO [sql_server_chronics] ([datetime])
VALUES('#{datetime}')")

Possible solution ??:
@connection.execute("INSERT INTO [sql_server_chronics]
([datetime]) VALUES('#{@connection.quoted_date(datetime)}')")

Change the test, so that it will quote the values on inserting,
BUT, this will require, that quoted_date will handle string
parameters:

def quoted_date(value)
if value.acts_like?(:time)
if value.respond_to?(:usec) && value.usec != 0
if sqlserver_2008?
value.iso8601(6) # SQL Server 2008 handles Time Zone
offsets and 6? digits for second fractions
else
value.utc.iso8601(3)
end
else
if sqlserver_2008?
value.iso8601
else
value.utc.iso8601
end
end
elsif value.acts_like?(:date)
sprintf("%04d%02d%02d", value.year, value.month, value.day)
elsif value.class == String
quoted_date(Time.parse(value))
else
super
end
end
---------------------------------------------------------------------------------------------

Problem 2:
For tables with timestamp columns (created_at, updated_at) Rails
seems to put the timestamp as a String object in the attributes.
So ActiveRecord#quote recevies a String and will NOT quote it with
quoted_date:
See my trace of running the test:

>>> insert_fixture( #<Fixture:0x2787200 @connection=#<ActiveRecord::ConnectionAdapters::SQLServerAdapter version: 3.0.0.beta1, year: 2005, connection_options: {:adapter=>"sqlserver", :mode=>:odbc, :host=>"localhost", :username=>"rails", :dsn=>"activerecord_unittest", :database=>"activerecord_unittest", :password=>""}>, @fixture={"id"=>1, "name"=>"David", "salary"=>80000, "created_at"=>"2010-06-29 13:18:17", "updated_at"=>"2010-06-29 13:18:17"}, @model_class=Developer(id: integer, name: string, salary: integer, created_at: datetime, updated_at: datetime)>, developers)
ActiveRecord#quote(1,
#<ActiveRecord::ConnectionAdapters::SQLServerColumn:0x277ff40>)
class:Fixnum
ActiveRecord#quote(David,
#<ActiveRecord::ConnectionAdapters::SQLServerColumn:0x277fcc0>)
class:String
ActiveRecord#quote(80000,
#<ActiveRecord::ConnectionAdapters::SQLServerColumn:0x277f9a0>)
class:Fixnum
ActiveRecord#quote(2010-06-29 13:18:17,
#<ActiveRecord::ConnectionAdapters::SQLServerColumn:0x277f6a0>)
class:String
ActiveRecord#quote(2010-06-29 13:18:17,
#<ActiveRecord::ConnectionAdapters::SQLServerColumn:0x277a240>)
class:String
>>>execute(INSERT INTO [developers] ([id], [name], [salary], [created_at], [updated_at]) VALUES (1, 'David', 80000, '2010-06-29 13:18:17', '2010-06-29 13:18:17'), Fixture Insert)

ActiveRecord::StatementInvalid: ODBC::Error: 22008 (242) [Microsoft]
[SQL Native Client][SQL Server]Bei der Konvertierung eines char-
Datentyps in einen datetime-Datentyp liegt der datetime-Wert auࠥrhalb
des g��gen Bereichs.: INSERT INTO [developers] ([id], [name],
[salary], [created_at], [updated_at]) VALUES (1, 'David', 80000,
'2010-06-29 13:18:25', '2010-06-29 13:18:25')


Greetings

Klaus

PS: I am down to :
169 tests, 416 assertions, 4 failures, 7 errors, 0 skips

[Ken Collins]

Klaus,

BTW, for longer code fragments we may want to use gists or some other online paste tool. Helps keep email lean and formatting intact.

>> {"isolation_level"=>"read committed", "concat_null_yields_null"=>"SET", "quoted_identifier"=>"SET", "ansi_padding"=>"SET", "ansi_null_dflt_on"=>"SET", "datefirst"=>"7", "language"=>"us_english", "textsize"=>"64512", "lock_timeout"=>"-1", "ansi_nulls"=>"SET", "dateformat"=>"mdy", "ansi_warnings"=>"SET"}
>>
>> Perhaps we can easily case off that "dateformat" to help the adapter cast correctly? Seems to me we could easily do that in the #quoted_date for the inbound data and then the coercion of the ODBC::TimeStamp on the way out.
>
> In my test system I have changed #quoted_date , so that it will
> convert the data to ISO8601 format which is independent of the
> language settings. BUT
>
> Problem 1:
> - it won't catch situations where someone is using connection.execute
> See your test 'For chronic data types' :
> @connection.execute("INSERT INTO [sql_server_chronics] ([datetime])
> VALUES('#{datetime}')")
>
> Possible solution ??:
> @connection.execute("INSERT INTO [sql_server_chronics]
> ([datetime]) VALUES('#{@connection.quoted_date(datetime)}')")

That is not a problem, we just change the test to insert correctly. We never want to get into examining other peoples SQL on raw execute, etc. So problem 1 is moot and easily solvable.

At first glance this looks heavy handed with to many conditions and the specific case for 2008 mixed all over. I am also not sure about that forced cast to UTC. That would assume that people have that config in active record for that zone, which we should not assume. Lemme run some tests... yea, more than half of my suite is failing now. Over 1,400 errors. Most saying "Conversion failed when converting date and/or time from character string"... '2005-07-15T10:28:00.009900-04:00'

I would suggest taking a different approach. Did you try my recommendation of examining the dateformat from the user options or some other reflection means and casing off of that? I would also suggest memorizing that method too so we are not doing a user options query for every quote value.

That is true and not a problem that the adapter or we should try to monkey patch. Many people are used to doing #to_s(:db). Seems to me if your DB is set to not accept that format, the end user would be better redefining the :db formatter for Time/Date conversions, maybe even just delegating to iso8601. The adapter would never make an assumption to do something so bold, but that is perfectly reasonable for an end user setup/trump. We should only be in the business of quoting of Date/Time objects.

I am sure there is a clean simple solution and I think your getting close. I have a topic branch dedicated just for testing your patches. I'll help too. Can we move comments to the ticket and start swapping diffs for me to test, comment and make recommendations on? FYI, I am also in the #rails-sqlserver IRC room more often lately and can be found on skype/aim too.

- Ken