Wrong datetime comparison

Question

1.00/5 (1 vote)

See more:

C#

var db = new XDataContext();
var q = from t in db.Posts select t;
var to = new DateTime(2011, 2, 1).AddMilliseconds(-1);          
q = q.Where(n => n.Date <= to);
foreach (var qq in q)
	// I get FEBRUARY items here!!!

Why do I get February itmes her?? I Know, it could be done differently, but I'm puzzled why it fails when coded this way. Note: types involved here are all datetime.

Thanks

Julio

Posted 27-Jul-11 7:12am

Julio Kuplinsky

Updated 27-Jul-11 7:14am

v2

Add a Solution

Comments

Joris Janssens 27-Jul-11 15:18pm

You shouldn't get items from feb 2011 or later with that code. One question though: are the february items you get from 2011 (which shouldn't happen) or from earlier years (totally normal)?

Julio Kuplinsky 27-Jul-11 16:05pm

from 2011

Joris Janssens 27-Jul-11 16:30pm

very weird, I replicated your code and it's working normally here.

1. does "Console.WriteLine(to.ToString("yyyy-MM-dd HH:mm:ss"));" outputs "2011-01-31 23:59:59"
2. does it work normally when you replace "q = q.Where(n => n.Date <= to);" with the following code:
List<datetime> oldDates = new List<datetime>();
foreach (DateTime n in q) {
if (n.Date <= to) {
oldDates.Add(n);
}
}
foreach (var qq in oldDates) {
//output
}
This also has the benefit that you can step through it to see where it goes horribly wrong. But again, I see now flaw in your code.

Julio Kuplinsky 28-Jul-11 11:18am

1. 23:59:59 doesn't look right either, if you are subtracting one *MILLI*second.
2. I don't see that your code focuses on the problem. It seems to me that the SQL engine mishandles the conversion of the C# datatype DateTime.

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Praveen Kullu · Answer 1 · 2011-07-27T07:21:00

Solution 1

What is the result you are expecting?

The DateTime struct show values in this format:

C#

DateTime(yyyy,mm,dd)

Since month is 2 in your code, it will show values from february.

Posted 27-Jul-11 7:21am

Praveen Kullu

RaisKazi · Answer 2 · 2011-07-27T07:43:00

Solution 2

You have initialized your DateTime variable with 01-Fab-2011.

Even if you substract 1 Millisecond, it still remains 01-Fab-2011.

Hence you are getting "February" items.

Make sure if you want to substract Days/Months or something else.

Posted 27-Jul-11 7:43am

RaisKazi

Updated 27-Jul-11 7:50am

v2

Comments

Julio Kuplinsky 27-Jul-11 13:51pm

1) Really! it remains 1-feb-2011 what time exactly?
2) why does it work different if i subtract one second?

Joris Janssens 27-Jul-11 15:12pm

"Even if you substract 1 Millisecond, it still remains 01-Fab-2011. "

That's not correct. If you subtract one millisecond you end up with 2011-Jan-31 23:59:59. I would have used "DateTime to = new DateTime(2011, 2,1)" as end date and "n.Date < to" though, it's a bit cleaner.

Monjurul Habib · Answer 3 · 2011-07-27T08:31:00

Solution 3

Navigate the following links and get the C# DateTime basics.
DateTime.AddMilliseconds Method
DateTime.AddSeconds Method

Finally
DateTime Structure

System.DateTime Methods (LINQ to SQL)

Posted 27-Jul-11 8:31am

Monjurul Habib

Updated 27-Jul-11 12:23pm

v3

Comments

Julio Kuplinsky 27-Jul-11 14:51pm

this doesn't help at all. there's nothing wrong with the c# datatypes. It's LINQ/SQL that returns the wrong records. In fact,if i subtract one second i dont get any februaries.

Monjurul Habib 27-Jul-11 18:23pm

look at my last link.