How do compare dates in LINQ?

I am trying compare dates in a LINQ query and I keep getting the following error:

"The conversion of a varchar data type to a datetime data type resulted in an out-of-range value."

The date value in dateLabel is in the format dd/mm/yyyy e.g. 17/08/2010.  Even if I substitue 'DataTime date = DateTime.Now' I still get the conversion error.

The Date field in the database is definitely of type DateTime.  An example of such a field that exists in the database is 2010-11-09 16:00:00.000.

Any ideas?
protected void Page_Load(object sender, EventArgs e)
    {
        DateTime date = Convert.ToDateTime(dateLabel.Text.Trim());

        matchesListView.DataSource = GetMatches(date);
        matchesListView.DataBind();
    }

    protected IQueryable GetMatches(DateTime date)
    {
        //Create a new instance of the TournamentDataContext object.
        TournamentDataContext db = new TournamentDataContext();
        //Get all matches
        IQueryable matches = from m in db.Matches
                             where (m.Date >= date && m.Date <= date.AddHours(24))
                             select new
                             {
                                 MatchDay = m.MatchDay.Title,                                 
                                 Date = string.Format("{0:D}", m.Date),
                                 Time = string.Format("{0:t}", m.Date),
                                 TeamAFlagIconUrl = m.dw_Team.FlagIconUrl,
                                 TeamAName = m.dw_Team.Name,
                                 TeamBFlagIconUrl = m.Team.FlagIconUrl,
                                 TeamBName = m.Team.Name
                             };

        //Return the IQueryable Match object.
        return matches;
    }

Open in new window

markerasmusAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Meir RivkinFull stack Software EngineerCommented:
try replace this line: where (m.Date >= date && m.Date <= date.AddHours(24))with this line: where (DateTime.Parse(m.Date.ToString()) >= date && DateTime.Parse(m.Date.ToString()) <= date.AddHours(24))
0
p_davisCommented:
have you tried

DateTime.TryParse(...
0
markerasmusAuthor Commented:
@sedgwick:  I get "Specified cast is invalid"
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Meir RivkinFull stack Software EngineerCommented:
what is the value of m.Date?
what is the value of m.Date.ToString()
0
Meir RivkinFull stack Software EngineerCommented:

what is the value TYPE of m.Date?
0
markerasmusAuthor Commented:
m.Date is System.DataTime
m.Date = 25/08/2010 20:00
m.Date.ToSting() = Aug 25 2010 8:00PM

I have attached some screenshots of the database design and results, and a capture of LINQ results.
MatchDesign.PNG
Match.PNG
LINQQueryVisulizer.PNG
0
Snarf0001Commented:
I'd be about 95% sure that the date format in your database server is different from the dateformat on your pc.

ie the server is set to mm/dd/yyyy while the pc is set to dd/mm/yyyy.

The LINQ query is still just writing out a sql query with the date comparison in quotes, so it's failing becuase of a switch on the format when it executes the query.

Can you tell me which of the following succeeds when executed against the database?

select * from matches where matchday > '25/08/2010'
select * from matches where matchday > '08/25/2010'
0
markerasmusAuthor Commented:
The second one works.  The first one throws the conversion error.
0
Snarf0001Commented:
Give me a bit to try to recall how I fixed this...
That is the problem, when the linq engine sends off the query, its using the local formatting, so its turning this:

IQueryable matches = from m in db.Matches
                             where (m.Date >= date && m.Date <= date.AddHours(24))


into something like this:

select * from Matches m where m.date >= '25/08/2010' and m.date........

And throwing the error right there when the db tries to convert.
0
Snarf0001Commented:
If by chance this is an asp.net and you have control over the server, it's as easy as swapping out the regional format on that computer.
But I'm assuming that would break a lot of other things you have going on, so as I said, need to remember what I did.
0
vivekpv10Commented:
I will show u an example.Here i going to comapre current time is greater than that of what in db.for that firs i convert current time to same format as that of db.
After this i will use linq query for comparing.
             string compare = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss");
             DateTime DtCompare = DateTime.Parse(cutime.ToString(compare));
var Result=(from DEV in context.device  where DEV.device.deviceid == devid && DEV.exdate >DtCompare   select new{DEV.lgid});
0
markerasmusAuthor Commented:
@Snarf0001: I am running the ASP.NET application locally with a local database so I am free to make changes to the config. However, I don't want to run into the same problem in the production environment.

I checked my SQL server's date format using DBCC USEROPTIONS and it was set to mdy.  Then I ran SET DATEFORMAT dmy; but it is still erroring.

The SQL query LINQ is generating is:
...
WHERE ([t0].[Date] >= '25/08/2010 00:00:00') AND ([t0].[Date] <= '26/08/2010 00:00:00')

If I change it to the following it works (in SQL)
...
WHERE ([t0].[Date] >= '08/25/2010 00:00:00') AND ([t0].[Date] <= '08/26/2010 00:00:00')

Am I setting it in the wrong place?
0
markerasmusAuthor Commented:
By the way I am based in the UK so the dmy format is what I'm after.
0
Snarf0001Commented:
You're on the right track, but not quite correct.  SET DATEFORMAT
0
Snarf0001Commented:
sorry, SET DATEFORMAT only applies to each connection, not the server.
To reset the server format, you need to run sp_configure, please see the following link.

http://support.microsoft.com/kb/173907

There's instructions specific to british (luckily enough) under "Set the Language on the SQL Server".

If you can check the prod db server and the prod. web server and verify they're both the same (as I would expect), then just use the above to get your local db to the format you need for developing.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.