nhibernate, sql server Datetime compare failing

Question

In nhibernate i am using following to compare date

Restrictions.Eq(Projections.Property("SubmittedDate"), request.Data.SubmittedDateTime.Value)

in local environment its working. but in other environment its not returning result.

SubmittedDateTime? is nullable

do i need to mention nullable somewhere in mapping file.

Is there any change i need to do in code for this?

Do you need to compare also time? if not then try to use request.Data.SubmittedDateTime.Value.Date. Try to look at this post: http://stackoverflow.com/questions/2872444/round-net-datetime-milliseconds-so-it-can-fit-sql-server-milliseconds — Marian Ban, Jun 04 '12 at 10:42
SubmittedDateTime is C# nullable datetime type. and SQL type is (Datetime,null) — Techmaster, Jun 04 '12 at 11:59
@StefanSteinegger SubmittedDateTime is C# nullable datetime type. and SQL type is (Datetime,null) — Techmaster, Jun 04 '12 at 15:26

score 0 · Answer 1 · answered Sep 26 '12 at 21:20

Stephen,

Your problem may be (or have been) that your production server has ANSI nulls enabled [1]: http://msdn.microsoft.com/en-us/library/ms188048.aspx. In such cases, one NULL value is not the same as another. e.g.

DECLARE @a INT,
        @b INT

SELECT @a = null, @b=null

IF(@a=@b)
BEGIN
    SELECT 'Equal'
ELSE
    SELECT 'NOT Equal'
END

If you run the code below on an environment with ANSI NULLS ON, you would see 'NOT Equal'

It could also be that your dates have a time component set;

2012-09-26 22:16:00.000 <> 2012-09-26

For this, you would need to use DATEDIFF(D, Date1, Date2)=0.

NHibernate does not support this out the box, so you would need to create (or find) a custom ICriterion

1 Answers1