asked on

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

Meir Rivkin

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

p_davis

have you tried

DateTime.TryParse(...

markerasmus

ASKER

@sedgwick: I get "Specified cast is invalid"

Meir Rivkin

what is the value of m.Date?
what is the value of m.Date.ToString()

Meir Rivkin

what is the value TYPE of m.Date?

markerasmus

ASKER

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

Snarf0001

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'

markerasmus

ASKER

The second one works. The first one throws the conversion error.

Snarf0001

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.

Snarf0001

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.

vivekpv10

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});

markerasmus

ASKER

@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?

markerasmus

ASKER

By the way I am based in the UK so the dmy format is what I'm after.

Snarf0001

You're on the right track, but not quite correct. SET DATEFORMAT

ASKER CERTIFIED SOLUTION

Snarf0001

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial