[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

How do compare dates in LINQ?

Posted on 2010-08-17
15
Medium Priority
?
2,599 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 3
  • +2
15 Comments
 
LVL 42

Expert Comment

by:Meir Rivkin
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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 42

Expert Comment

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

Expert Comment

by:Meir Rivkin
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 23

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
 

Author Comment

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

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 23

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 23

Expert Comment

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

Accepted Solution

by:
Snarf0001 earned 2000 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

649 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