CODEWITHSUNDEEP
c#linq-to-sqlsql-server-2008
AndrewC
AndrewC

Reputation: 6730

Linq to SQL DateTime2 query

I have a table with a datetime2 field and I need to get all rows out of it where the date is today. Rather oddly (in my opinion but I'm sure there's a valid reason for it) if I do:

MyTable.Where(t => t.Date == DateTime.Today).ToList()

it returns nothing even though there are entires with todays date.

What am I missing here? I thought that datetime2 allowed you to query like this instead of having to use greater than and less than to specify a timeframe?

Edit

I've tried using the .Date portion of the DateTime2 representation in Linq to SQL:

MyTable.Where(t => t.Date.Date == DateTime.Today).ToList()

but I'm still getting nothing. Yet in my database there are rows with the value 2011-08-05 00:00:00.0000000 which is clearly today.

Edit again I've ran the query:

List<string> dates = MyTable.Select(t => t.Date.Date.ToString()).ToList();

and I'm getting results like 2011-08-05, so that portion obviously works.

However, when I run 

DateTime.Today.Date.ToString()

I get 08/05/2011 00:00:00. Could the addition of this time portion be causing the issue? How would I remove this?

Edit 3

Got it to work using the code:

MyTable.Where(t => t.Date.Date.ToString() == DateTime.Today.Date.ToString("yyyy-dd-MM")).ToList();

This seems hacky though (converting to a string before comparison) and surely there must be a cleaner way?

Upvotes: 0

Views: 2166

Answers (2)

Lee Gunn
Lee Gunn

Reputation: 8666

It sounds like the date in the database isn't actually today (8th May). It's probably 5th August.

Upvotes: 1

Anthony Faull
Anthony Faull

Reputation: 17997

It looks like your datetime2 field is called Date. You need to use the Date property of this Date field to ignore the time of day.

MyTable.Where(t => t.Date.Date == DateTime.Today).ToList()

Upvotes: 0

Related Questions