Linq to SQL DateTime2 query

Question

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?

@Conrad yea there's a time stored too, but I just want everything today. — AndrewC
– AndrewC, Commented May 8, 2011 at 15:01
You need to get a look at the raw sql that L2S is generating. — Lee Gunn
– Lee Gunn, Commented May 8, 2011 at 15:18
Do you have the code that is actually inserting the DateTime into the datebase. Can you confirm that the date in the db is definitely 8th May rather than 5th August? — Lee Gunn
– Lee Gunn, Commented May 8, 2011 at 18:40

Lee Gunn · Accepted Answer · 2011-05-08 18:43:20Z

1

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

answered May 8, 2011 at 18:43

Lee Gunn

8,6944 gold badges42 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

AndrewC Over a year ago

that's what I'm thinking. how can i change that?

Lee Gunn Over a year ago

We need to see the code that is inserting the date time. Or did you just use Management Studio to get some test data in there...?

Lee Gunn Over a year ago

Ha - easy mistake to make...at least it's fixed now.

Anthony Faull · Accepted Answer · 2011-05-08 14:59:03Z

0

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()

answered May 8, 2011 at 14:59

Anthony Faull

18.1k5 gold badges58 silver badges75 bronze badges

4 Comments

Lee Gunn Over a year ago

would it not need to be: MyTable.Where(t => t.Date.Date == DateTime.Today.Date).ToList()

vcsjones Over a year ago

@Lee: DateTime.Today is the same as DateTime.Now.Date, you don't need to .Date since Today doesn't include the time.

AndrewC Over a year ago

I've edited the question a few times there guys with more info.

Lee Gunn Over a year ago

@vcsjones ah yeah - in my head I must of saw DateTime.Now - thanks

Collectives™ on Stack Overflow

Linq to SQL DateTime2 query

2 Answers 2

3 Comments

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

4 Comments

Related