Solved

How do compare dates in LINQ?

Posted on 2010-08-17
15
2,434 Views
Last Modified: 2013-11-11
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

0
Comment
Question by:markerasmus
  • 5
  • 5
  • 3
  • +2
15 Comments
 
LVL 42

Expert Comment

by:sedgwick
ID: 33453768
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
 
LVL 22

Expert Comment

by:p_davis
ID: 33453774
have you tried

DateTime.TryParse(...
0
 

Author Comment

by:markerasmus
ID: 33453790
@sedgwick:  I get "Specified cast is invalid"
0
 
LVL 42

Expert Comment

by:sedgwick
ID: 33453804
what is the value of m.Date?
what is the value of m.Date.ToString()
0
 
LVL 42

Expert Comment

by:sedgwick
ID: 33453807

what is the value TYPE of m.Date?
0
 

Author Comment

by:markerasmus
ID: 33453952
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
 
LVL 22

Expert Comment

by:Snarf0001
ID: 33454005
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:markerasmus
ID: 33454060
The second one works.  The first one throws the conversion error.
0
 
LVL 22

Expert Comment

by:Snarf0001
ID: 33454094
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
 
LVL 22

Expert Comment

by:Snarf0001
ID: 33454104
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
 
LVL 5

Expert Comment

by:vivekpv10
ID: 33454140
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
 

Author Comment

by:markerasmus
ID: 33454264
@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
 

Author Comment

by:markerasmus
ID: 33454273
By the way I am based in the UK so the dmy format is what I'm after.
0
 
LVL 22

Expert Comment

by:Snarf0001
ID: 33454657
You're on the right track, but not quite correct.  SET DATEFORMAT
0
 
LVL 22

Accepted Solution

by:
Snarf0001 earned 500 total points
ID: 33454681
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

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Welcome my friends to the second instalment and follow-up to our Minify and Concatenate Your Scripts and Stylesheets (http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/A_4334-Minify-and-Concatenate-Your-Scripts-and-Stylesheets.html)…
For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
This video discusses moving either the default database or any database to a new volume.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now