NHibernate criteria datetime format issue

Question

Hi

I have a simple nHibernate criteria query which is looking for records with a datetime less than today:

example:

criteria.Add(Expression.Le("TheDate", DateTime.Today));

However, the results I am getting are incorrect when the day/month is ambiguous (eg 12th November 2009 returns the records for 11th December 2009)

I have looked at the SQL generated and the datetime format will show 12/11/2009, and if I run the sql query directly in SQL Manager it will only return the correct results with the date format 11/12/2009.

Is there a way of defining the SQL date format from the criteria expression or do I need to do something to the database table?

Many thanks

Answer 1

There isn't a specific setting in NHibenrate to do with date formats and locales. The issue is with the DB. In my system and without any specific settings I am getting dates out of my NHibernate queries as @p2='2009-11-13 12:48:22:000'. This is in yyyy-mm-dd and I am not sending dates in this way from my code nor I instruct NHibernate to do that explicitly.

This is happening in the DB and it is related to the SQL Server's instance default language and/or the user account's language setting that the NHibenrate's DB connection is using. I believe it maybe also related to the operating system's date format settings in conjunction with the SQL Server instance's default language.

In SQL Server Management Studio you can right click on the instance to change/fix the language from the Advanced Settings tab in the Properties dialog. You will also have to go and change/fix the default language that is used for the user account NHibernate is using (get it from the connection string). On your Server Instance expand Security->Logins and right click on the correct login to pop up the Properties dialog. In the general tab there is a "Default Language" setting you can set.

I had that issue before and setting the languages in SQL Server as "British English" solved the issue (I am located in the UK). Your case maybe similar with the one I solved this way.

Answer 2

Many thanks for your reply, I thought it maybe something like the location settings.

I actually worked round the problem using HQL, but I will keep this in mind.

Cheers T