CODEWITHSUNDEEP
c#linqdatetimeasp.net-web-api2
Mr.Smithyyy
Mr.Smithyyy

Reputation: 1329

Operator >= cannot be applied to operands of type string and datetime

The user enters two parameters in the url which are the start date and end date and they are entered in the format yyyyMMddhhmm as a string. I'm attempting to take these strings and turn them into dates so I can query my database.

[ResponseType(typeof(Detail))]
public IHttpActionResult GetDetail(string StartDate, string EndDate)
{
    DateTime StartDateTime;
    DateTime EndDateTime;

    StartDateTime = new DateTime();
    EndDateTime = new DateTime();

    StartDateTime = DateTime.ParseExact(StartDate, "yyyyMMddhhmm", null);
    EndDateTime = DateTime.ParseExact(EndDate, "yyyyMMddhhmm", null);

    var detail = from a in db.Details where (a.callDate >= StartDateTime && a.callDate <= EndDateTime) select a;

    var Response = new DetailResponse() { status = true, calls = detail };
    return Ok(response);
}

However I get the error that >= can't be used in datetime and strings.

EDIT: For the sake of one of the answer I'm including a model class I'm using to display the data.

DetailResponse.cs

public class DetailResponse
{
    public bool status { get; set; }
    public string statusMessage { get; set; }
    public IQueryable<Detail> calls { get; set; }
}

Upvotes: 3

Views: 18325

Answers (4)

Christos
Christos

Reputation: 53958

Probably this is happening, because callDate is a string. So you can't compare a string with a datetime. The solution to this problem is to have the same type. That being said I would convert a.callDate to a DateTime.

However, I think that it would be better you change the data type of callDate in the database level. Undoubtedly, this is a personal opinion. So you don't have to follow it. Doing so your code will not need any change.

Now, in terms of code the solution I suggested above is the following:

var allDetails = db.Details.AsEnumerable();
var details = from detail in details
              let callDate = DateTime.ParseExact(detail.callDate, "yyyyMMddhhmm", null)
              where callDate >= StartDateTime 
              && callDate <= EndDateTime
              select detail;

Update

As we concluded in comments, we had to call the AsEnumerable, in order the above query to work. Why is this needed?

Borrowing Jon Skeet's words from Reimplementing Linq to Objects: Part 36 – AsEnumerable

Now it’s not entirely uncommon to want to perform some aspects of the query in the database, and then a bit more manipulation in .NET – particularly if there are aspects you basically can’t implement in LINQ to SQL (or whatever provider you’re using). For example, you may want to build a particular in-memory representation which isn’t really amenable to the provider’s model.

The DateTime.ParseExact cannot be translated properly in a database method.

Upvotes: 8

ChrisF
ChrisF

Reputation: 137148

As has already been said, you can't compare a string to a DateTime, however, given that the date format is

yyyyMMddhhmm

(ie. year month day hour minute) where the values are all numeric and go from least varying -> most varying you will be safe doing a string comparison:

var detail = from a in db.Details where (a.callDate >= StartDate && a.callDate <= EndDate) select a;

This is because "201601010101" is less than "201612312359" when comparing strings (in the same way that "a" is less than "b").

This will save you converting the data to DateTime.

Having said that, by doing the conversion you are validating the data and can show an error if it's not in the right format.

Upvotes: 0

Simon Karlsson
Simon Karlsson

Reputation: 4129

Your comparison is failing because the date in your database is of type string, try doing like this:

[ResponseType(typeof(Detail))]
public IHttpActionResult GetDetail(string StartDate, string EndDate)
{
    DateTime StartDateTime = DateTime.ParseExact(StartDate, "yyyyMMddhhmm", null);
    DateTime EndDateTime = DateTime.ParseExact(EndDate, "yyyyMMddhhmm", null);

    var detail = from a in db.Details where (DateTime.ParseExact(a.callDate, "yyyyMMddhhmm", null) >= StartDateTime && 
                    DateTime.ParseExact(a.callDate, "yyyyMMddhhmm", null) <= EndDateTime) select a;
}

However you are probably best off chaning the type of your callDate to a date instead of a string.

Upvotes: 1

Gilgamesh
Gilgamesh

Reputation: 668

What does your schema look like? is callDate a string? you may need to convert callDate to a DateTime before you can do the comparison.

var detail = from a in db.Details where (Convert.ToDateTime(a.callDate) >= StartDateTime &&  Convert.ToDateTime(a.callDate) <= EndDateTime) select a;

Upvotes: 0

Related Questions