Nhibernate Datetime issues

Question

hi there,

i'm currently experiencing a little problem. I'm using nhibernate with around 40 entities mapped to a sql server database. however, due to performance reasons some operations are done via stored procedure. i have a stored procedure that takes 3 parameters. one entity ID and 2 DateTime's.

now, when i have a Date like the 10th of Dezember 2008, Nhibernate makes

'10.12.2008 00:00:00.000'

out of it. (German Culture) But Sql Server 2008 doesn't interpret it correctly as it swaps Day and Month. When i manually change the datetime text to

'2008-12-10 00:00:00.000'

i didn't encounter this problem when using the session in a "normal" way or when using NHibernate.linq. just when using a sql-query to execute the stored procedure.

is there a way to manually overcome the way NHibernate transforms DateTime objects to their textual representation?

Answer 1

NHibernate shouldn't be transforming DateTimes into their textual representation at all, IMO - it should be asking the database driver to pass that DateTime along in whatever form it wants to.

Is your stored procedure declared to receive the parameter as DateTimes (or whatever the SQL type is) or varchar? If if's declared as receiving a DateTime and NHibernate knows that, then it should just be using parameterised call as normal.

Answer 2

i pass the datetimes with .SetDateTime(name,datetimeValue) to the IQuery, therefore i thought nhibernate should take care of it. the stored procedure expects DateTime parameters. i just saw this textual representation when i turned show_sql on

Answer 3

I'm seeing the same thing with a single DateTime in a being passed into a CLR stored proc. Mine is also multiple parameters. I've tried it as a string, as well, and i get errors no matter what I do. Here's the code from the mapping file:

" exec spGetRentRollByDate :@propertyID :@reportDate "

The stored proc first looks for a resultset in a given table, and if it's not there, it cfires off another function to create one and returns that set instead.