DateTime Comparison in LINQ

I am ran into the following issue.

Take a look at the following where clause.

Where(x => x.CreateTS <= dateParameters.CreationDate.ToDateValue &&
           x.CreateTS >= dateParameters.CreationDate.FromDateValue)

CreateTS is a Timestamp column in the table. so when my dateparameters are todate = 01/28/2010 12:00A.M" fromdate= "01/26/2010 12.00A.M" (c# DateTime types) my query doesnot retrivies the table records whose CreateTS look like 01/28/2010 1:45A.M and above just differeing in the timestamps.

I开发者_C百科 just wanted to do comparison and dont want to compare the timestamps. any help would be appreciated.

Just look at the .Date portion of the DateTime:

Where(x => x.CreateTS.Date <= dateParameters.CreationDate.ToDateValue && x.CreateTS.Date >= dateParameters.CreationDate.FromDateValue)

As a side note:

Personally, I would put from first, since it's a bit easier to follow from a readability standpoint:

Where(x => x.CreateTS.Date >= dateParameters.CreationDate.FromDateValue && x.CreateTS.Date <= dateParameters.CreationDate.ToDateValue)

(I find it easier to think of being between two values as being >low and

Edit: You didn't specify that you were using the EF LINQ providers, which do not allow you to do Date. You can, I believe, handle this by looking forward one day, and using < instead of <= for your "to" comparison:

Where(x => x.CreateTS >= dateParameters.CreationDate.FromDateValue && x.CreateTS.Date < dateParameters.CreationDate.AddDays(1).ToDateValue)

@SARAVAN, AFAIK timestamp columns cannot be used in comparisons like that. A timestamp columns are mapped as byte[] (byte array) in EF. If comparisons like that are needed, I suggest to change the timestamp column in database to a datetime one.

only to quote from MSDN:

The SQL Server timestamp data type has nothing to do with times or dates. SQL Server timestamps are binary numbers that indicate the relative sequence in which data modifications took place in a database.

Interesting moment here is that 01/28/2010 12:00A.M actually in 24 hour format is 00:00 while 01/28/2010 1:45A.M is 01:45 and that seems to be the reason why those records are not returned. The midday will be 01/28/2010 12:00P.M