[Mono-bugs] [Bug 500987] SqlCommand with DateTime parameter throws exception "Error converting data type varchar to datetime" (impacts NHibernate and Castle ActiveRecord)

[bugzilla_noreply at novell.com]

http://bugzilla.novell.com/show_bug.cgi?id=500987

User loic.nageleisen at gmail.com added comment
http://bugzilla.novell.com/show_bug.cgi?id=500987#c9





--- Comment #9 from Loic Nageleisen <loic.nageleisen at gmail.com>  2009-05-15 02:08:03 MDT ---
I noticed that in a certain context, some dates were inserting correctly, while
some failed. My hopes were that those were the all-time great classics with
days >12 but of course, no such luck. Here is how my reasoning went, and the
progress so far:

First take note that @p2 is SqlDbType.DateTime

Then, have the cultureinfo match the database language settings:

Thread.CurrentThread.CurrentCulture = new Culture("en-US")
new SqlCommand("SET LANGUAGE us_english", connection)

Then, have a loop which insert each day in a range of dates (for example a
whole year).

With the above locale settings (US implies a mdy date format), every single
insertion fail.
With 'en-GB' and 'British English' (implies a dmy date format), every single
insertion fail.
With 'fr-FR' and 'Français', (implies a dmy date format), regardless of year
(starting at year 1753, as per MSSQL requirement), days whose month is 3, 5, 6
or 8 do insert, and the *correct value* is stored in the database, while all
others fail. Years prior to 1753 fail for any month.

In fact, as long as LANGUAGE is set to 'Français', and regardless of the
CultureInfo, the third case behavior occurs.

Failures all come from the database with the aforementioned exception stack and
message 'Error converting data type varchar to datetime' (text dependent of SET
LANGUAGE value).

Again take note that @p2 is SqlDbType.DateTime. Relating that to the error
message, I wonder why does the database want to parse a *varchar* to a datetime
when the object is supposed to be passed on to the database as a DateTime?

If I then modify @p2 to be a SqlDbType.VarChar (all else being equal), all
insertions are successful regardless of the language, as long as CultureInfo
and LANGUAGE match. Being a VarChar, this behavior is correct and makes sense,
as they have to communicate data in the same language to be able to generate
and parse the string.

This last point is of course the whole point of using SqlDbType.DateTime
instead of SqlDbType.VarChar, as the result should then be independent of
client and server locale settings. Trouble is:
1. it doesn't work.
2. behavior varies with database LANGUAGE setting.

This is weird, and veers towards borderline insane knowing you can't replicate
it, as I can't see what's different in our setups apart from the fact that I
run the MSSQL servers on French Windows variants, but can't begin to think why
that should be relevant.

-- 
Configure bugmail: http://bugzilla.novell.com/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the QA contact for the bug.